SUM() subquery for total money spent for each customer-CodePudding

I'm stuck on a problem and I need help. So, I need to find the total money spent per customer. In the database, one customer_id has multiple payments. This is my code:

"SELECT customer.first_name, customer.last_name, customer.customer_id, address.address, city.city, address.postal_code, SUM(amount) as money_spent
FROM customer, address, city, payment
WHERE customer.address_id = address.address_id
AND address.city_id = city.city_id
GROUP BY customer_id
ORDER BY customer.last_name ASC;");

However, that column only repeats the total amount for all customers. How to fix this?

CodePudding user response：

The problem with this query is here:

FROM customer, address, city, payment
WHERE customer.address_id = address.address_id
AND address.city_id = city.city_id

You're missing the AND payment.customer_id = customer.id part for the payment table. In this case, the database joins all payments for each customer. So when you get the result, you're getting the total sum for all payments with each customer.

Note: the AND payment.customer_id = customer.id part depends on the database structure. Update payment.customer_id and customer.id accordingly.