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."'"
>Solution :
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))