WITH ROLLUP总计和小计(WITH ROLLUP GRAND TOTAL AND SUBTO

我有一个产生的结果集几乎是有一个脚本！ 我试图让小计和总计。 我上一年柱，并在年底总计子的总数。 我的目标是得到最终结果国家“总计”，而不是小计。 请注意，我的最后一行，“位置”也返回为空，由于汇总功能。

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

Answer 2:

您需要使用CASE WHEN（GROUPING（的ColumnName）= 1），就像这样：

SELECT
  CASE WHEN GROUPING(YEAR) = 1 AND GROUPING(LOCATION) = 1 THEN 'grand total'
       WHEN GROUPING(YEAR) = 1 AND GROUPING(LOCATION) <> 1 THEN 'sub total'
       ELSE YEAR END AS YEAR
  COUNT(ACCOUNTS) AS 'ACCOUNTS',        
  SUM(BALANCE) as 'BAL',
  CASE WHEN GROUPING(LOCATION) = 1 THEN 'ALL' ELSE 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

文章来源: WITH ROLLUP GRAND TOTAL AND SUBTOTAL