select data along with sum of a column
What would be the best sql to get data out from the table below as follows
Example table
id name buyer amt
1 per disney 10
1 per marie 12
1 per clarence 11
2 pella Magnus 5
2 pella Olof 2
2 pella Asa 开发者_高级运维 4
3 chris Lotta 6
3 chris Wayne 3
3 chris Brad 5
Data output:
Customer Name : Per
disney 10
marie 12
clarence 11
Total 33
Customer Name : pella
Magnus 5
Olof 2
Asa 4
Total 11
Customer Name : Chris
Lotta 6
Wayne 3
Brad 5
Chris 14
So in the end its the name at the top, and every buyer for that seller and each transaction and finally the total.
I came upwith this sql query, but its rather crude.
select name, supplier, amt, (select sum(amt) from transact where name = t.name)as total from transact t
Use Group By and with Rollup to get the sums.
select name, supplier, amt
from transact t
group by name, supplier with rollup;
http://dev.mysql.com/doc/refman/5.1/en/group-by-modifiers.html
精彩评论