带汇总总计和小计

我有一个脚本可以生成几乎已经存在的结果集！我正在尝试获取小计和总计。我在年份栏中得到了小计，在最后得到了总计。我的目标是让最终结果显示“总计”而不是小计。请注意，由于汇总函数，我的最后一行“位置”也返回为空。

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

您可以使用分组ID http://technet.microsoft.com/en-us/library/bb510624.aspx识别每行聚合的分组集

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