MySQL Forums
Forum List  »  Newbie

Case When help
Posted by: Tim O'Neill
Date: July 24, 2021 03:49PM

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

Options: ReplyQuote


Subject
Written By
Posted
Case When help
July 24, 2021 03:49PM
July 24, 2021 06:29PM
July 25, 2021 01:31AM
July 25, 2021 11:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.