如何在按年月分组的SQL查询中新增所有分组count(*)的总和

该需求完全可以实现，以下提供两种适配不同场景的写法：

方案1：通用兼容写法（支持所有支持CTE的数据库，如MySQL、PostgreSQL、Oracle等） #

先通过CTE计算每个年月的分组统计结果，再通过UNION ALL拼接总计数行：

WITH month_count AS (
    SELECT 
        COUNT(*) AS record_count,
        EXTRACT(MONTH FROM my_table_date) AS stat_month,
        EXTRACT(YEAR FROM my_table_date) AS stat_year
    FROM my_table
    GROUP BY stat_year, stat_month
)
-- 原有年月分组统计结果
SELECT record_count, stat_month, stat_year FROM month_count
UNION ALL
-- 拼接总计数行，月份和年份字段用NULL占位
SELECT SUM(record_count) AS total_count, NULL AS stat_month, NULL AS stat_year FROM month_count
ORDER BY stat_year NULLS LAST, stat_month NULLS LAST;

返回结果会在原有分组数据的最后追加一行，月份和年份列为空，第一列即为所有分组的计数总和。

方案2：ROLLUP简化写法（支持PostgreSQL 9.5+、MySQL 8.0+、Oracle等支持ROLLUP语法的数据库） #

利用GROUP BY的ROLLUP扩展直接生成分层聚合结果，写法更简洁：

SELECT 
    COUNT(*) AS record_count,
    EXTRACT(MONTH FROM my_table_date) AS stat_month,
    EXTRACT(YEAR FROM my_table_date) AS stat_year
FROM my_table
-- ROLLUP会自动生成按「年+月」、「年」、「全局」的三层聚合结果
GROUP BY ROLLUP (stat_year, stat_month)
-- 过滤保留原有年月分组+全局总聚合，去掉每年的聚合行（不需要过滤可删除该行）
HAVING stat_month IS NOT NULL OR (stat_month IS NULL AND stat_year IS NULL)
ORDER BY stat_year NULLS LAST, stat_month NULLS LAST;

如果需要优化展示效果，可以用COALESCE函数将总计数行的空年月字段替换为「总计」等自定义标识，根据你使用的数据库语法调整即可。

内容的提问来源于stack exchange，提问作者mlwacosmos