sub totals in a group by

Question

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 1

You could use GROUP BY GROUPING SETS

CREATE TABLE #MyTable( Place VARCHAR(10), Date VARCHAR(20), Total1 INT, Total2 INT, Total3 INT ) 

INSERT INTO #MyTable ( place, [date], Total1, Total2, Total3 )
VALUES(      '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)


SELECT  
   [Date]
   , ISNULL( Place  , 'Total' )
   , SUM( Total1 ) AS Total1
   , SUM( Total2 ) AS Total2
   , SUM( Total3 ) AS Total3
FROM #MyTable
GROUP BY GROUPING SETS
 ( Place, [Date] )
, ( [Date] ) 


DROP TABLE #MyTable

Answer 2

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