CODEWITHSUNDEEP
mysql
Niels
Niels

Reputation: 1006

SQL getting sum and all rows in one go

I am trying to get the total sum of the column amount and all the rows at the same time. 

SELECT sum(orders.amount) as total, orders.transactionID, orders.itemid, products.id, 
products.category, products.price 
FROM db.orders 
INNER JOIN db.products 
ON orders.itemid = products.id 
WHERE transactionID = '012906ea51c14b0881c4785527c606dd'

The query above only gives me the total value and only one row (I also want to have all the rows)

SELECT sum(orders.amount) as total, orders.transactionID, orders.itemid, products.id, 
products.category, products.price 
FROM db.orders 
INNER JOIN db.products 
ON orders.itemid = products.id 
WHERE transactionID = '012906ea51c14b0881c4785527c606dd'
GROUP BY orders.id

While the second query gives me every row but incorrect total of orders.amount

Can this be done?

(I made the two queries a little shorter)

Thanks

Upvotes: 0

Views: 536

Answers (1)

fancyPants
fancyPants

Reputation: 51878

You can use a "running total", meaning, using a variable and summarize the values yourself. The correct sum will appear in the last row of your result set.

SELECT @my_sum := @my_sum + orders.amount as running_total, 
orders.transactionID, 
orders.itemid, 
products.id, 
products.category, 
products.price 
FROM db.orders 
INNER JOIN db.products 
ON orders.itemid = products.id 
CROSS JOIN (SELECT @my_sum:=0) AS var_init_subquery
WHERE transactionID = '012906ea51c14b0881c4785527c606dd'
  • read more about user variables here

Also have a look at Raymond Nijland's comment:

Query 1.. SUM without GROUP BY always generates 1 row. Query 2 is a invalid use off GROUP BY read https://www.psce.com/en/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/

Upvotes: 2

Related Questions