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';
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