Get sum of records if value exists consider as 1 not exact value-CodePudding

I need "Open" as 2 and click as 1 But now I'm getting like open as 5 and click as 2

"SELECT 
COALESCE(sum(requested),0) as requested,
COALESCE(sum(processed),0) as processed,
COALESCE(sum(open),0) as open,
COALESCE(sum(click),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."'"

CodePudding user response：

sum() works as expected. If you wish to treat anything above 0 as 1, then write if condition like below. That being said, coalesce looks redundant here then.

sum(if(open is not null and open > 0, 1, 0)),
sum(if(click is not null and click > 0, 1, 0))