Total sum of a column in a table that's used created by groupby

Question

For the example below, I'm wondering how to obtain the total (i.e. the sum of all of the rows in SUM(total_cost). It seems like this should be easy but I've been trying for quite some time now and am still stuck. Any help would be greatly appreciated and I apologize for the "noobishness" of this question.

SELECT cate_id,SUM(total_cost)
FROM purchase            
GROUP BY cate_id;

+---------+-----------------+
| cate_id | SUM(total_cost) |
+---------+-----------------+
| CA001   |         1725.00 | 
| CA002   |          965.00 | 
| CA003   |          900.00 | 
+---------+-----------------+

Are you looking for something like this? | CA001 | 3590.00 | | CA002 | 3590.00 | | CA003 | 3590.00 | Then Check this, Similar question https://stackoverflow.com/questions/11357640/using-sum-without-grouping-the-results — Valli, Oct 10 '17 at 23:35

score 3 · Accepted Answer · answered Oct 10 '17 at 21:39

3

You can use WITH ROLLUP:

SELECT cate_id, SUM(total_cost)
FROM purchase            
GROUP BY cate_id WITH ROLLUP;

This will add a new row with the total.

answered Oct 10 '17 at 21:39

Gordon Linoff

1,242,037
58
646
786

WITH ROLLUP was an extremely helpful term to lookup, thanks a lot! – urandom Oct 11 '17 at 15:48

score 1 · Answer 2 · answered Oct 10 '17 at 21:51

1

For a general solution across RDBMs, consider UNION query without grouping:

SELECT cate_id, SUM(total_cost)
FROM purchase            
GROUP BY cate_id

UNION ALL

SELECT NULL, SUM(total_cost)
FROM purchase

answered Oct 10 '17 at 21:51

Parfait

104,375
17
94
125

score 0 · Answer 3 · answered Oct 10 '17 at 22:14

0

Unless you really need to use the group by or need to show all of the ids the cleanest solution should be:

select sum(total_cost) from purchase;

This gives the output : 3590

answered Oct 10 '17 at 22:14

Sleepy

1
2

Total sum of a column in a table that's used created by groupby

3 Answers3