Tuesday, 10 September 2013

Calculate proper total in query?

Calculate proper total in query?

I have a table like this
| customer_id | item_id | price | amount |
I want to retrieve the amount of money each customer has spent in a single
query.
I have tried:
SELECT SUM(price * amount) AS total FROM table GROUP BY customer_id
but this spits out astronomically high values for total. It cannot be
correct.
I also tried
SELECT @total := @total + (price * amount) AS total FROM table
CROSS JOIN (SELECT @total := 0) CONST
GROUP BY customer_id
but this doesn't start from 0 for each customer, so the previous totals
will stack up...
How do I properly retrieve the data I want?

No comments:

Post a Comment