SQL 汇总总计和小计
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20135033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
WITH ROLLUP GRAND TOTAL AND SUBTOTAL
提问by Chase Cabrera
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.
我有一个脚本可以生成一个几乎就在那里的结果集！我正在尝试获得小计和总计。我在年份列中得到小计，在最后得到总计。我的目标是让最终结果表明“总计”而不是小计。请注意，由于汇总功能，我的最后一行 '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
result set...
结果集...
YEAR ACCOUNTS BAL LOCATION
---- -------- --------- --------
NULL 11 80687.51 WA
NULL 107 592980.18 NULL
Desired result set...
想要的结果集...
YEAR ACCOUNTS BAL LOCATION
---- -------- --------- --------
sub total 11 80687.51 WA
grand total 107 592980.18 ALL
回答by Anon
You can use GROUPING_IDto identify the grouping set each row is aggregating
您可以使用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
回答by cha
You need to use CASE WHEN (GROUPING(ColumnName) = 1), like this:
您需要使用 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
