分组记录的求和与计数：如何升级SQL实现分组汇总

架构老林

2026-8-3

如何在SQL中实现分组后添加分组汇总行？

原始数据表

Col1	Col2	Col3
R1	C1	5
R2	C3	8
R1	C1	2
R1	C2	4
R2	C5	3
R2	C2	4

需求

按Col1和Col2分组，统计每组的记录数（Count）以及Col3的SUM值；
在上述分组结果基础上，按Col1相同值汇总Count列的总和与Sum列的总和，生成带汇总行的结果。

已实现需求1的SQL语句

SELECT Col1, Col2, COUNT(*) AS Count, SUM(Col3) AS Sum
FROM myTable 
GROUP BY Col1, Col2

需求1的返回结果

Col1	Col2	Count	Sum
R1	C1	2	7
R1	C2	1	4
R2	C2	1	4
R2	C3	1	8
R2	C5	1	3

需求2的预期结果

Col1	Col2	Count	Sum
R1	C1	2	7
R1	C2	1	4
		3	11
R2	C2	1	4
R2	C3	1	8
R2	C5	1	3
		3	15

解决方案

方法1：使用`ROLLUP`（适用于MySQL 8.0+、SQL Server、PostgreSQL等支持SQL:2003标准的数据库）

ROLLUP可自动生成分组汇总行，配合GROUPING函数区分明细与汇总行：

SELECT 
  CASE WHEN GROUPING(Col2) = 1 THEN '' ELSE Col1 END AS Col1,
  CASE WHEN GROUPING(Col2) = 1 THEN '' ELSE Col2 END AS Col2,
  COUNT(*) AS Count,
  SUM(Col3) AS Sum
FROM myTable
GROUP BY Col1, Col2 WITH ROLLUP
HAVING GROUPING(Col1) = 0
ORDER BY Col1, GROUPING(Col2);

GROUPING(Col2) = 1标识该行是同一Col1下所有Col2的汇总行
HAVING GROUPING(Col1) = 0过滤掉全表总汇总行，仅保留各Col1的分组汇总
ORDER BY确保明细行在前，对应汇总行在后

方法2：通用`UNION ALL`方法（适用于所有数据库）

先获取分组明细，再单独统计各Col1的汇总，最后合并结果并排序：

SELECT Col1, Col2, Count, Sum
FROM (
  -- 分组明细行
  SELECT Col1, Col2, COUNT(*) AS Count, SUM(Col3) AS Sum, 1 AS sort_order
  FROM myTable
  GROUP BY Col1, Col2
  UNION ALL
  -- 各Col1的汇总行
  SELECT Col1, '', SUM(Count) AS Count, SUM(Sum) AS Sum, 2 AS sort_order
  FROM (
    SELECT Col1, Col2, COUNT(*) AS Count, SUM(Col3) AS Sum
    FROM myTable
    GROUP BY Col1, Col2
  ) t
  GROUP BY Col1
) combined
ORDER BY Col1, sort_order;