如何调整含SUM与CASE WHEN的GROUP BY查询实现按阶段聚合

我编写了如下SQL查询：

Select a.ordernum, a.region, c.area, a.branchname, a.stagename,
sum(case when b.entrydatetime is not null THEN 1 ELSE 0 END) as 'Total Count',
sum(case when DATEDIFF(day, b.entrydatetime, GETDATE()) <=1 THEN 1 ELSE 0 END) as 'WITHIN 1 DAY',
sum(case when DATEDIFF(day, b.entrydatetime, GETDATE()) >1 AND DATEDIFF(day, b.entrydatetime, GETDATE()) <=2 
THEN 1 ELSE 0 END) as 'WITHIN 2 DAYS'
FROM DATAtBLE a, datatbleb b, datatblec c where a.ordernum = b.ordernumber and a.ordernum = c.ordernum 
and a.region in ('India','US','Pakistan') and c.area in ('Chennai','West Bengal','New York') and a.branchname in ('Brooklyn','Navi Mumbai')
order by a.region,  c.area,  a.branchname, a.stagename,a.ordernum
group by a.region,  c.area,  a.branchname, a.stagename,a.ordernum

当前查询输出 #

期望输出 #

调整方案 #

要实现按stagename及关联维度汇总的效果，只需对原查询做两处修改：

1. 移除SELECT子句中的a.ordernum：不需要显示单个订单号，因此从查询结果中去掉该字段
2. 修改GROUP BY和ORDER BY子句：移除a.ordernum，只保留需要作为汇总分组的维度：a.region, c.area, a.branchname, a.stagename

修改后的完整SQL #

Select a.region, c.area, a.branchname, a.stagename,
sum(case when b.entrydatetime is not null THEN 1 ELSE 0 END) as 'Total Count',
sum(case when DATEDIFF(day, b.entrydatetime, GETDATE()) <=1 THEN 1 ELSE 0 END) as 'WITHIN 1 DAY',
sum(case when DATEDIFF(day, b.entrydatetime, GETDATE()) >1 AND DATEDIFF(day, b.entrydatetime, GETDATE()) <=2 
THEN 1 ELSE 0 END) as 'WITHIN 2 DAYS'
FROM DATAtBLE a, datatbleb b, datatblec c 
where a.ordernum = b.ordernumber and a.ordernum = c.ordernum 
and a.region in ('India','US','Pakistan') 
and c.area in ('Chennai','West Bengal','New York') 
and a.branchname in ('Brooklyn','Navi Mumbai')
group by a.region, c.area, a.branchname, a.stagename
order by a.region, c.area, a.branchname, a.stagename

说明 #

原查询的GROUP BY包含了ordernum，这会让每个订单号单独成为一个分组，导致结果按订单号拆分。移除ordernum后，SQL会按照region、area、branchname、stagename的组合进行分组，sum函数会自动计算每个分组内的合计值，从而得到你需要的汇总结果。

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