sql - 分组依据中的小计

Question

我有一个按地名和 yyyy-mm 分组的查询。我想要的是每个月的 3 个地名的总和。例如，看下面，3 月份“total1”的 3 个“位置”总数为 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 

像这样

    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

您可以使用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