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


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

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."'"


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

Answered By – nice_dev

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published