SQL使用SUM与CASE WHEN查询时Revenue_ND和Revenue_Home_Care结果一致排查

错误原因 #

• 第一，COUNT(CASE WHEN ... THEN 1 ELSE 0 END)写法错误：COUNT函数统计所有非空值，不符合条件返回的0也会被计入，最终ND和Home_Care字段值都等于该门诊总有效行数，两者数值完全相等。如果要统计符合条件的行数，要么删除ELSE 0分支（不符合条件返回NULL不被COUNT计数），要么将COUNT替换为SUM。
• 第二，核心统计逻辑错误：子查询中的Revenue是该门诊全类别预约的总收入，未拆分ND类、居家护理类的独立收入，外层用总收入分别除以数值相等的ND、Home_Care计数，自然得到完全相同的结果，完全不符合「统计不同类别对应收入」的需求。

正确查询方案 #

你不需要嵌套子查询，直接在主查询中按类别条件分别求和对应收入即可，参考代码如下：

SELECT 
  COALESCE(businesses.label, 'total') AS Clinic,
  SUM(CASE 
    WHEN appointment_types.name LIKE '%initial%' AND appointment_types.category IN (
        'National- Ex Phys- ND',
        'National- Physio -ND',
        'National- OT -ND',
        'National- Speech Pathology- ND',
        'SA/WA - Physiotherapy - ND',
        'SA/WA - Telehealth Physiotherapy - ND'
    ) THEN invoices.net_amount ELSE 0 
  END) AS Revenue_ND,
  SUM(CASE 
    WHEN appointment_types.name LIKE '%initial%' AND appointment_types.category IN (
        'National- Zest',
        'National- OT -HCP',
        'National- Physio -HCP',
        'National- Physio -Remedy',
        'National-Physio-TUH',
        'National- Speech Pathology- HCP'
    ) THEN invoices.net_amount ELSE 0 
  END) AS Revenue_Home_Care
FROM 
  individual_appointments
  INNER JOIN appointment_types ON appointment_types.id = individual_appointments.appointment_type_id
  INNER JOIN invoices ON invoices.appointment_id = individual_appointments.id
  INNER JOIN businesses ON businesses.id = individual_appointments.business_id
WHERE
  {{clinic}} 
  AND {{date}}
GROUP BY ROLLUP(businesses.label);

补充说明 #

如果你实际需求是统计两类服务的单均收入，可以对上述代码做调整，用对应类别的总收入除以该类别的有效预约数即可，注意加NULLIF避免除以0报错，示例如下：

-- 单均ND收入示例
SUM(CASE WHEN ND条件 THEN invoices.net_amount ELSE 0 END) / NULLIF(SUM(CASE WHEN ND条件 THEN 1 ELSE 0 END), 0) AS Avg_Revenue_ND

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