CODEWITHSUNDEEP
sqlcount
sudeep
sudeep

Reputation: 79

Calculating Counts in SQL query and comparing them in same query

I would like to get the count of rows with specific criteria and then compare the counts in the same query. How can i do this ?

I need to get the list of devices for which started column is OK and tested column is failed. I am not sure how to loop thru entire table to get these count comparsion done.

SELECT type,device,
 (SELECT (COUNT(*) WHERE Started = "OK" from table1) AS t1, 
 (SELECT (COUNT(*) WHERE tested = "FAILED" from table1) AS t1
 from table1
      where remediated = "Remediated"
      GROUP BY type,device
      HAVING (SELECT (COUNT(*) WHERE Started = "OK" from table1) = (SELECT (COUNT(*) WHERE tested = "FAILED" from table1)

Upvotes: 1

Views: 129

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Use conditional aggregation:

SELECT type, device,
       SUM(CASE WHEN Started = 'OK' THEN 1 ELSE 0 END) as num_ok,
       SUM(CASE WHEN Started = 'FAILED' THEN 1 ELSE 0 END) as num_failed
FROM table1
WHERE remediated = 'REMEDIATED'
GROUP BY type, device
HAVING num_ok = num_failed;

This does not do exactly what your query does, because it is filtering on remediated for the counts as well as the rows. However, I suspect this is what you intend.

Note: Not all databases support table aliases in the HAVING. You can just repeat the expressions if need be:

HAVING SUM(CASE WHEN Started = 'OK' THEN 1 ELSE 0 END) = SUM(CASE WHEN Started = 'FAILED' THEN 1 ELSE 0 END);

Upvotes: 1

Related Questions