SQL查询需求：将存在值的字段按1计数求和而非实际值

我需要统计出“Open”的结果为2，“Click”为1，但当前SQL对open和click字段的实际数值求和，得到的结果是Open为5、Click为2，不符合需求。

需求是：只要open/click字段存在有效值（非空且非0），就按1计数，而非累加字段的实际数值。

修改后的SQL语句 #

"SELECT 
COALESCE(sum(requested),0) as requested,
COALESCE(sum(processed),0) as processed,
COALESCE(SUM(CASE WHEN open IS NOT NULL AND open <> 0 THEN 1 ELSE 0 END),0) as open,
COALESCE(SUM(CASE WHEN click IS NOT NULL AND click <> 0 THEN 1 ELSE 0 END),0) as click,
COALESCE(sum(dropped),0) as dropped,
COALESCE(sum(delivered),0) as delivered,
COALESCE(sum(spamreport),0) as spamreport,
COALESCE(sum(deferred),0) as deferred,
COALESCE(sum(bounce),0) as bounce,
COALESCE(sum(unsubscribe),0) as unsubscribe
FROM CAMPAIGN_REPORTS WHERE _kftCampaingID='".$campaignId."'"

说明 #

• 用SUM(CASE ...)的方式，判断字段是否存在有效值（非空且不等于0），满足条件则计1，否则计0，最后求和得到符合需求的计数结果。
• 如果需求是只要字段不为空就计数（不管字段值是否为0），可以直接用COUNT(open)和COUNT(click)替换对应的SUM(...)部分，比如：COALESCE(COUNT(open),0) as open，因为COUNT函数会自动忽略NULL值，统计非NULL的行数。

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