-1

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.

jr20
  • 11
  • 2
  • What is the error? – PeterClemmensen Nov 17 '22 at 21:38
  • Probably you were getting an error that you didn't use the fields you selected in your group by clause. Number of fields you have in your select except aggregate function, you have to have that in your group by clause. more read? here https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns – N Subedi Nov 17 '22 at 21:40

1 Answers1

0

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
GMB
  • 216,147
  • 25
  • 84
  • 135
nbk
  • 45,398
  • 8
  • 30
  • 47