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

user_5689797 0
Viewed 28

enter image description here

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."'"
mysql
1 Answers

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))
user_49648220
Related