Abdalla Ismail Abdalla Ismail - 19 days ago 6
SQL Question

Aggregate Where on multiple select query sql

So I am using this query to count the the number of authentication errors in my database :

SELECT (
SELECT COUNT(audit.server_response_code)
FROM audit Where audit.server_response_code = '401'
) AS Locked,
(
SELECT COUNT(audit.server_response_code)
FROM audit Where audit.server_response_code = '403'
) AS unlocked,
(
SELECT COUNT(audit.server_response_code)
FROM audit Where audit.server_response_code = '490'
) AS Passforget,
(
Select Count (audit.server_response_code)
From audit where audit.server_response_code = '491'
) AS invalid


The query is working fine , however i want to add an aggregate where statement that applies on the four statements.How can i do it?

Answer

Use case expressions to do conditional counting:

select count(case when audit.server_response_code = '401' then 1 end) AS Locked,
       count(case when audit.server_response_code = '403' then 1 end) AS unlocked,
       count(case when audit.server_response_code = '490' then 1 end) AS Passforget,
       count(case when audit.server_response_code = '491' then 1 end) AS invalid 
from audit
where audit.server_response_code in ('401','403','490','491')

The WHERE clause is perhaps not needed, but may speed things up (depending on data and indexes.)