基于Kaggle员工数据集，如何用SQL ROLLUP给透视表添加总计行？

我用员工数据集，基于EmployeeClassificationType和EmployeeStatus创建了如下透视表：

with pivotTable as 
(
    select 
        EmployeeClassificationType, [Active], [Future Start], 
        [Leave of Absence], [Terminated for Cause], 
        [Voluntarily Terminated],
        ([Active] + [Future Start] + [Leave of Absence] + [Terminated for Cause] + [Voluntarily Terminated]) as total
    from 
        (select 
             EmployeeStatus, EmployeeClassificationType, count(*) as total
         from  
             employee_data
         group by 
             EmployeeStatus, EmployeeClassificationType) a
    pivot
        (sum(total)
            for employeeStatus in ([Active], [Future Start], [Leave of Absence], [Terminated for Cause], [Voluntarily Terminated])
        ) as pivotTable
)
-- rollup and find the grand total as well as add a row to total 
select * 
from pivotTable

当前输出没有总计行，我想要添加类似预期效果的总计行，但尝试以下ROLLUP语句后未得到预期结果：

尝试语句1：

select 
    coalesce(EmployeeClassificationType, 'Grand Total') as EmployeeClassificationType,
    sum(Active) as Active,
    sum([Future Start]) as [Future Start],
    sum([Leave of Absence]) as [Leave of Absence],
    sum([Terminated for Cause]) as [Terminated for Cause],
    sum([Voluntarily Terminated]) as [Voluntarily Terminated],
    sum(total) as 'Grand Total'
from 
    pivotTable
group by 
    rollup(EmployeeClassificationType, Active, [Future Start], [Leave of Absence], [Terminated for Cause]), [Voluntarily Terminated]

尝试语句2：

select 
    *,
    sum(total) as 'Grand Total'
from 
    pivotTable
group by 
    rollup(EmployeeClassificationType, Active, [Future Start], [Leave of Absence], [Terminated for Cause]), [Voluntarily Terminated], total

需要帮助实现正确的总计行添加。

你的问题出在ROLLUP的分组字段选择错误——不需要把所有状态字段都加入ROLLUP分组，只需要按EmployeeClassificationType进行ROLLUP即可，因为我们要的是按分类的小计和全局总计。

方法1：使用ROLLUP实现 #

修改后的SQL如下：

with pivotTable as 
(
    select 
        EmployeeClassificationType, 
        [Active], 
        [Future Start], 
        [Leave of Absence], 
        [Terminated for Cause], 
        [Voluntarily Terminated],
        ([Active] + [Future Start] + [Leave of Absence] + [Terminated for Cause] + [Voluntarily Terminated]) as total
    from 
        (select 
             EmployeeStatus, EmployeeClassificationType, count(*) as total
         from  
             employee_data
         group by 
             EmployeeStatus, EmployeeClassificationType) a
    pivot
        (sum(total)
            for employeeStatus in ([Active], [Future Start], [Leave of Absence], [Terminated for Cause], [Voluntarily Terminated])
        ) as pivotTable
)
select 
    coalesce(EmployeeClassificationType, 'Grand Total') as EmployeeClassificationType,
    sum(Active) as Active,
    sum([Future Start]) as [Future Start],
    sum([Leave of Absence]) as [Leave of Absence],
    sum([Terminated for Cause]) as [Terminated for Cause],
    sum([Voluntarily Terminated]) as [Voluntarily Terminated],
    sum(total) as total
from pivotTable
group by rollup(EmployeeClassificationType)

说明：

• ROLLUP(EmployeeClassificationType)：仅对分类字段进行ROLLUP，会生成每个分类的明细行（因pivotTable已按分类聚合，sum结果即为原数值），以及一行EmployeeClassificationType为NULL的总计行。
• coalesce函数：将NULL替换为'Grand Total'，明确标注总计行。
• 所有状态字段和total均用sum聚合，确保总计行为各分类数值的总和。

方法2：使用UNION ALL追加总计行 #

如果需要保留原透视表的明细行结构，同时单独追加总计行，这种方式更直观：

with pivotTable as 
(
    select 
        EmployeeClassificationType, 
        [Active], 
        [Future Start], 
        [Leave of Absence], 
        [Terminated for Cause], 
        [Voluntarily Terminated],
        ([Active] + [Future Start] + [Leave of Absence] + [Terminated for Cause] + [Voluntarily Terminated]) as total
    from 
        (select 
             EmployeeStatus, EmployeeClassificationType, count(*) as total
         from  
             employee_data
         group by 
             EmployeeStatus, EmployeeClassificationType) a
    pivot
        (sum(total)
            for employeeStatus in ([Active], [Future Start], [Leave of Absence], [Terminated for Cause], [Voluntarily Terminated])
        ) as pivotTable
)
-- 原透视表明细行
select * from pivotTable
union all
-- 总计行
select 
    'Grand Total' as EmployeeClassificationType,
    sum(Active),
    sum([Future Start]),
    sum([Leave of Absence]),
    sum([Terminated for Cause]),
    sum([Voluntarily Terminated]),
    sum(total)
from pivotTable

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