汇总总计和小计 [英] WITH ROLLUP GRAND TOTAL AND SUBTOTAL

问题描述

我有一个脚本，它生成的结果集几乎就在那里！我正在尝试获得小计和总计.我在年份列中得到小计，在最后得到总计.我的目标是让最终结果表明总计"而不是小计.请注意，由于 rollup 函数，我的最后一行 'location' 也返回 null.

I have a script that produces a result set that is almost there! I'm trying to get subtotals and grand totals. I get sub totals on the year column and a grand total at the end. My goal is to get the final result to state "grand total" instead of subtotal. Please note that my final row, 'location' also returns as null due to the rollup function.

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

推荐答案

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

You can use GROUPING_ID to identify the grouping set each row is aggregating

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

这篇关于汇总总计和小计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！