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

Issue

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

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

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