SQL - Using 2 where clauses in same table

Question

Im trying to create a result that looks like the following

The queries for both would be

SELECT COUNT(AC) AS QUALIFIED FROM CTE WHERE QUALCODE='QUALIFIED';

SELECT COUNT(AC) AS NONQUALIFIED FROM CTE WHERE QUALCODE='NONQUALIFIED';

But how do I use them both in the same query and table?

Thanks

Answer 1

Use case expressions to do conditional counting:

select count(case when QUALCODE='QUALIFIED' then AC end) as QUALIFIED,
       count(case when QUALCODE='NONQUALIFIED' then AC end) as NONQUALIFIED 
from CTE

Edit:

How would introduce a third column where it is the sum of both columns i.e Total 66?

select count(case when QUALCODE='QUALIFIED' then AC end) as QUALIFIED,
       count(case when QUALCODE='NONQUALIFIED' then AC end) as NONQUALIFIED,
       count(AC) as total_ac
from CTE

Alternatively, use a derived table:

select QUALIFIED, NONQUALIFIED, QUALIFIED + NONQUALIFIED as total_ac
from
(
    select count(case when QUALCODE='QUALIFIED' then AC end) as QUALIFIED,
           count(case when QUALCODE='NONQUALIFIED' then AC end) as NONQUALIFIED
    from CTE
) dt

SQL - Using 2 where clauses in same table

Sign up