Sum of count with where query

December 10, 2023

I’m trying to divide two count using where conditions

(SELECT COUNT([Main_Status]) 
 FROM [V_Incidents_Combined] 
 WHERE [Main_Status] = N'open') / 
(SELECT COUNT([Main_Status]) 
 FROM [V_Incidents_Combined] 
 WHERE [Main_Status] = NT'Closed')

I get this error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘/’

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.
Visit Medevel

>Solution :

You have two subqueries, but you’re not doing anything with the result of the calculation of those.

You need to select the final result

SELECT (
    SELECT Count(vic.Main_Status)
    FROM V_Incidents_Combined vic
    WHERE vic.Main_Status = N'open'
   ) / (
    SELECT Count(vic.Main_Status)
    FROM V_Incidents_Combined vic
    WHERE vic.Main_Status = N'Closed'
   );

Having said that, you can do this more efficiently in a single level using conditional aggregation.

Also note that since Main_Status is not null, the result of COUNT(Main_Status) will be the same as COUNT(*). And COUNT returns an integer, you probably want to make it into a decimal.

SELECT
    COUNT(CASE WHEN vic.Main_Status = N'open' THEN 1 END) * 1.0
  / COUNT(CASE WHEN vic.Main_Status = N'Closed' THEN 1 END)
FROM V_Incidents_Combined vic;