Cast with Aggregate problem

Transact-SQL
1

I have some code that works until I try to do a divide by with a case function.

Code is below and look for the comment does not wok.

SELECT
siteid,
linenum,
worktype,
COUNT(CASE WHEN status = 'APPR' THEN 1 ELSE NULL END) AS [Approved],
COUNT(CASE WHEN status = 'review' THEN 1 ELSE NULL END) AS [Review],
COUNT(CASE WHEN status = 'wmatl' THEN 1 ELSE NULL END) AS [WaitMatl],
COUNT(CASE WHEN status = 'comp' THEN 1 ELSE NULL END) AS [Complete],
COUNT(CASE WHEN status = 'incomp' THEN 1 ELSE NULL END) AS [InComplete],
COUNT(CASE WHEN status = 'closed' THEN 1 ELSE NULL END) AS [Closed],
COUNT(CASE WHEN status not in ('appr','wmatl') THEN 1 ELSE NULL END) AS [All_Completed],
Count () as allrecords,
/ The below divide by does not work /
--COUNT(CASE WHEN status not in ('appr','wmatl') THEN 1 ELSE null END) / Count () as Completion_Ratio
COUNT(CASE WHEN status not in ('appr','wmatl') THEN 'BLABLA' END) / Count (*) as Completion_Ratio

FROM workorder
WHERE (siteid in ('p202','p203','p201')) AND (worktype in ('mpm','ppm','tspm')) AND (istask ='0')
AND (historyflag ='0') AND (woclass = 'workorder') --AND (status not in ('comp','closed','review','incomp'))
AND (assetnum is not null) AND (maintby not in ('ms','ed'))
AND targcompdate < DATEADD(mm,DATEDIFF(mm,0,GETDATE())-0,0)
GROUP BY siteid,linenum,worktype
Order by siteid, linenum,worktype

2

What sort of does not work? COUNT will be using integer maths, and the COUNT of ('appr','wmatl') will be somewhat less than COUNT(*), so the result is presumably always 0 ?

You could CAST one of the two COUNTs as Numeric to force the arithmetic to be floating-point, rather than Integer, or you could just change:

...  / Count (*) as Completion_Ratio
to
... / (Count (*) + 0.0) as Completion_Ratio

personally I would use SUM rather than COUNT, because COUNT of NULL values will give warnings which an APP might mis-handle.

COUNT(CASE WHEN status = 'APPR' THEN 1 ELSE NULL END) AS [Approved],
becomes
SUM(CASE WHEN status = 'APPR' THEN 1 ELSE 0 END) AS [Approved],

Be interested to hear if anyone else has any opinions on Pros / Cons on COUNT vs. SUM for this job ??