SQL按ID分组计算TotalPerPax排除Sold为0行遇除零错误求解决方法

问题根因 #

你当前的SQL存在3个核心问题：

• 分组逻辑错误：你需要按ID维度聚合生成Tbl2，但GROUP BY中加入了Col1、Col2、Sold、Total字段，会保留Tbl1的逐行粒度，无法实现按ID汇总的效果
• 计算规则不匹配：原代码没有实现「总Total减去Sold为0的行的Total之和」的逻辑，直接用总Total除以总Sold
• 未做除零防护：当同ID的总Sold为0时，分母为0直接触发除以零报错

修复后代码 #

SELECT 
    ID,
    SUM(Sold) AS Sold,
    SUM(Total) AS Total,
    -- 如果需要总Sold为0时返回0而非NULL，可在外层套COALESCE: COALESCE(计算逻辑, 0)
    (SUM(Total) - SUM(CASE WHEN Sold = 0 THEN Total ELSE 0 END)) 
    / NULLIF(SUM(Sold), 0) AS TotalPerPax
FROM Tbl1
-- 需计算单个ID可保留WHERE条件，计算所有ID直接删除即可
-- WHERE ID = 1
GROUP BY ID

逻辑说明 #

• 仅保留ID作为分组维度，符合Tbl2按ID聚合的输出要求
• 用SUM(CASE WHEN Sold = 0 THEN Total ELSE 0 END)条件聚合得到同ID下Sold为0的行的Total总和，总Total减去该值得到计算TotalPerPax的分子
• 用NULLIF(SUM(Sold), 0)对分母做防护，总Sold为0时会转换为NULL，除法结果为NULL不会触发报错，可根据业务需要调整为返回0等默认值

执行上述代码即可得到你期望的Tbl2结果：

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