How can I get the sum total amount for each customer using SUM and Join function

Question

I hope you can help me with this.

In my table I need to get the total amount for each customer in SQL. How do I get the SUM and join function in the same query.

Table image (already joined two tables

Query:

SELECT customer.customer_id, first_name, last_name, amount FROM payment INNER JOIN customer ON customer.customer_id = payment.customer_id

Thanks!

I have tried to use SUM and Group by but I am still having syntax error.

Answer 1

If you put all three first columns in the GROUP BY you get no errors:

SELECT 
    customer.customer_id, first_name, last_name, SUM(amount) as total
FROM
    payment
        INNER JOIN
    customer ON customer.customer_id = payment.customer_id
GROUP BY customer.customer_id, first_name, last_name