添加带有总计的汇总行 [英] Add a summary row with totals

问题描述

我知道这听起来很疯狂，可能不应该这样做，但我需要这样的东西 - 我有来自 SELECT [Type], [Total Sales] From Before

I know this sounds crazy and probably should not be done this way but I need something like this - I have a records from SELECT [Type], [Total Sales] From Before

我想在末尾添加一个额外的行以显示表格末尾的 SUM (After).可以这样做吗?

I want to add an extra row at the end to show the SUM at the end of the table (After). Could this be done?

推荐答案

如果您使用的是 SQL Server 2008 或更高版本，您可以使用 ROLLUP() GROUP BY 函数:

If you are on SQL Server 2008 or later version, you can use the ROLLUP() GROUP BY function:

SELECT
  Type = ISNULL(Type, 'Total'),
  TotalSales = SUM(TotalSales)
FROM atable
GROUP BY ROLLUP(Type)
;

这假设 Type 列不能有 NULL，因此此查询中的 NULL 将指示汇总行，即具有总计的行.但是，如果 Type 列可以有自己的 NULL，则更合适的总行会计类型将类似于@Declan_K 的答案，即使用 GROUPING() 函数:>

This assumes that the Type column cannot have NULLs and so the NULL in this query would indicate the rollup row, the one with the grand total. However, if the Type column can have NULLs of its own, the more proper type of accounting for the total row would be like in @Declan_K's answer, i.e. using the GROUPING() function:

SELECT
  Type = CASE GROUPING(Type) WHEN 1 THEN 'Total' ELSE Type END,
  TotalSales = SUM(TotalSales)
FROM atable
GROUP BY ROLLUP(Type)
;

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