Count (subset of) grouped data

PHOTO EMBED

Thu Oct 27 2022 17:20:09 GMT+0000 (UTC)

Saved by @dhfinch #sql #sqlserver

SELECT CAST(dtDateTime as Date) As TheDate, count(*) as TotalRecs, 
COUNT(CASE WHEN nDBWTS_EffluentFlow<0 THEN 1 END) AS CountNegatives, 
Min(nDBWTS_EffluentFlow) as MinFlow, Max(nDBWTS_EffluentFlow) as MaxFlow, 
SUM(nDBWTS_EffluentFlow) as TotAllFlow, 
AVG(nDBWTS_EffluentFlow) as AvgAllFlow, 
AVG(nDBWTS_EffluentFlow)*0.001 * 60 * 1440 as AllEnvirSum, 
SUM(CASE WHEN nDBWTS_EffluentFlow>=0 THEN nDBWTS_EffluentFlow END) as TotPosFlow,
AVG(CASE WHEN nDBWTS_EffluentFlow>=0 THEN nDBWTS_EffluentFlow END) AS AvgPosFlow,
AVG(CASE WHEN nDBWTS_EffluentFlow>=0 THEN nDBWTS_EffluentFlow END)*0.001 * 60 * 1440  AS PosEnvirSum
FROM [EnvolvData].[dbo].[Environmental]
WHERE CAST(dtDateTime AS Date) BETWEEN '2022-09-01' AND '2022-09-20'
GROUP BY CAST(dtDateTime as Date)
ORDER BY CAST(dtDateTime as Date) ASC
content_copyCOPY

SUM(CASE WHEN nDBWTS_EffluentFlow>=0 THEN nDBWTS_EffluentFlow END) Because case has only one WHEN clause and no ELSE, it does not return values for the non-matching records. Count, Sum, Avg all work beautifully...