whitz11 whitz11 - 2 months ago 6
SQL Question

sub totals in a group by

I have a query which groups by place name and yyyy-mm. What I would like is to have a combined total of the 3 place names for each month. For example looking at the below the total of the 3 'places' for 'total1' in march would be 14, 4 + 5 + 5

place yyyy-mm total1 total2 total3
A 2016-03 4 4 5
B 2016-03 5 1 2
C 2016-03 5 4 2
A 2016-04 1 3 4
B 2016-04 2 3 4
C 2016-04 6 2 1


So something like this

place yyyy-mm total1 total2 total3
A 2016-03 4 4 5
B 2016-03 5 1 2
C 2016-03 5 4 2
ALL 2016-03 14 9 9
A 2016-04 1 3 4
B 2016-04 2 3 4
C 2016-04 6 2 1
ALL 2016-04 9 8 9

Answer

You could also use union all,but rollup is better in terms of readability and performance(not accessing table twice)

SELECT 
CASE WHEN PLACE IS NULL THEN 'ALL' ELSE  PLACE END as place,
YYYYMM,
SUM(TOTAL1) AS TOTAL1,SUM(TOTAL2) AS TOTAL2,SUM(TOTAL3) AS TOTAL3
FROM #TEMP
GROUP BY YYYYMM,PLACE
WITH ROLLUP
HAVING GROUPING(YYYYMM)=0

Output:

place   YYYYMM  TOTAL1  TOTAL2  TOTAL3
A       2016-03    4      4       5
B       2016-03    5      1       2
C       2016-03    5      4       2
ALL     2016-03    14     9       9
A       2016-04    1      3       4
B       2016-04    2      3       4
C       2016-04    6      2       1
ALL     2016-04    9      8       9
Comments