How to merge two tables and sum the values with the same ID in SQL?

I am new to SQL sorry if the question ends up being trivial.

I have two tables:

Table 1 “Clients”:

 CREATE TABLE customer (ID, Name, Surname);
 INSERT INTO customer (ID, Name, Surname)
 VALUES (1, 'Smith', 'Hulk'),
 (2, 'Jake', 'Brook'),
 (3, 'Vladimir', 'Computin'),
 (4, 'Joe', 'Door');

Table 2 “Payments”:

 CREATE TABLE payment (payment_id, customer_id, payment_date, payment_amount);
 INSERT INTO payment (payment_id, customer_id, payment_date, payment_amount)
 VALUES (1, 1, "11/09/2011", 100),
 (2, 2, "15/10/2013", 50),
 (3, 4, "2/1/2011", 30),
 (4, 3, "12/09/2011", 200),
 (5, 2, "15/10/2013", 200),
 (6, 3, "2/1/2011", 10),
 (7, 1, "11/09/2011", 120),
 (8, 4, "15/10/2013", 100);

Desired Output:

CREATE TABLE payment (Name, Surname, total_payments);
 INSERT INTO payment (Name, Surname, total_payments)
 VALUES ('Smith', 'Hulk',220),
 ('Jake', 'Brook', 250),
 ('Vladimir', 'Computin', 210),
 ('Joe', 'Door', 130)

Here is what I have tried:

SELECT payments.ID, sum(payments.payment_amount)
FROM payments
GROUP BY payments.ID

This sums the entries in table “payments” with the same ID.

However, I don’t know how to use the ID in order to have Name and Surname in front of total_payments (like in desired output).

Any help would be appreciated!

Answer

You can need JOIN to bring them into the query and then appropriate aggregation:

SELECT c.ID, c.Name, c.SurName, sum(p.payment_amount)
FROM clients c LEFT JOIN
     payments p
     ON c.id = p.customer_id
GROUP BY c.ID, c.Name, c.SurName;

Note: Using a correlated subquery is often faster:

SELECT c.*, 
       (SELECT SUM(p.payment_amount)
        FROM payments p
        WHERE c.id = p.customer_id
       ) as total_payments
FROM clients c ;

In particular, this can efficiently use an index on payments(customer_id, payment_amount).