sql - 汇总总计和小计

Question

我有一个脚本可以生成一个几乎就在那里的结果集!我正在尝试获得小计和总计。我在年份列中得到小计，在最后得到总计。我的目标是让最终结果表明“总计”而不是小计。请注意，由于汇总功能，我的最后一行 'location' 也返回 null。

SELECT
  YEAR,
  COUNT(ACCOUNTS) AS 'ACCOUNTS',        
  SUM(BALANCE) as 'BAL',
  LOCATION AS 'LOCATION'
FROM 
  ACCOUNT A 
WHERE C.CREATE BETWEEN 
  DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()-1),0) 
  AND DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
GROUP BY 
  LOCATION, YEAR
WITH ROLLUP

结果集...

YEAR  ACCOUNTS  BAL        LOCATION
----  --------  ---------  --------
NULL        11   80687.51  WA
NULL       107  592980.18  NULL

想要的结果集...

YEAR          ACCOUNTS  BAL        LOCATION
----          --------  ---------  --------
sub total           11   80687.51  WA
grand total        107  592980.18  ALL

Answer 1

您可以使用 GROUPING_ID识别每行聚合的分组集

SELECT
   CASE GROUPING_ID(LOCATION, YEAR)
     WHEN 0 THEN YEAR
     WHEN 2 THEN N'Sub total: ' + STR(YEAR) 
     WHEN 3 THEN N'Grand total'
  END
   COUNT(ACCOUNTS) AS 'ACCOUNTS',      
   SUM(BALANCE) as 'BAL',
   LOCATION AS 'LOCATION'
 FROM ACCOUNT A 
 WHERE C.CREATE BETWEEN DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()-1),0) 
                    AND DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
 GROUP BY LOCATION, YEAR
 WITH ROLLUP