Case When help
Hi all, I have the below code using case when. I'd like to modify this though so that instead of the code using 1 for true it counts the distinct number of records that match the criteria in each case when section.
If someone could please give me some example code?
SELECT `Name`, location_category_id, SUM(SlowDevFlag) AS Total_slow, SUM(FastDevFlag) AS Total_fast, SUM(RapidDevFlag) AS Total_rapid, SUM(UltraRapidDevFlag) AS Total_ultra_rapid
FROM (
SELECT
c.Name
,l.location_category_id
, CASE WHEN d.DeviceStatus != '99'
AND d.publishedstatus !='99'
AND d.LocationType NOT IN ('Home')
AND d.CountryCode = 'gb'
AND (SlowUnknown != 0
OR Slow1pSqe != 0
OR Slow1pYaz != 0
OR Slow1p5Men != 0)
AND (Fast1pCom = 0
AND Fast1pMen = 0
AND Rapid100CHA = 0
AND Rapid150CCS = 0
AND Rapid350CCS = 0)
AND RapidH35 = 0
AND RapidH70 = 0
THEN 1 ELSE 0 END AS SlowDevFlag
, CASE WHEN d.DeviceStatus != '99'
AND d.publishedstatus !='99'
AND d.LocationType NOT IN ('Home')
AND d.CountryCode = 'gb'
AND (Fast1pCom != 0
OR Fast3p22Tes != 0)
AND (Fast25CHA = 0
AND d.Fast25CCS = 0
AND Rapid3pMen = 0
AND RapidCHA = 0
AND Fast3pCom = 0
AND Rapid120TCCS = 0
AND Rapid150CCS = 0
AND Rapid350CCS = 0)
AND RapidH35 = 0
AND RapidH70 = 0
THEN 1 ELSE 0 END AS FastDevFlag
, CASE WHEN d.DeviceStatus != '99'
AND d.publishedstatus !='99'
AND d.LocationType NOT IN ('Home')
AND d.CountryCode = 'gb'
AND (Fast25CHA != 0
OR RapidCCS != 0)
AND (Rapid120TCCS = 0
AND Rapid150TCCS = 0
AND Rapid150CCS = 0
AND Rapid350CCS = 0)
AND RapidH35 = 0
AND RapidH70 = 0
THEN 1 ELSE 0 END AS RapidDevFlag
, CASE WHEN d.DeviceStatus != '99'
AND d.publishedstatus !='99'
AND d.LocationType NOT IN ('Home')
AND d.CountryCode = 'gb'
AND (Rapid120TCCS != 0
OR Rapid150TCCS != 0
OR Rapid150CCS != 0
OR Rapid350CCS != 0)
AND RapidH35 = 0
AND RapidH70 = 0
THEN 1 ELSE 0 END as UltraRapidDevFlag
FROM zm_devices d
LEFT JOIN `zm_locs` l on l.LocNum = d.LocNum
LEFT JOIN `zm_location_category` c on c.id = l.location_category_id
WHERE l.location_category_id IN ('24','177','252','286','186','174','292','180','171','168','183')
) flagged_data
GROUP BY NAME, location_category_id
ORDER BY NAME