Need to do aggregate function on same column multiple times with different where clauses on each result

Question

I am trying to create a query that gives me a count of the same column, but have 3 different columns displaying the results based on different where clauses. The below union all gives me the results but puts all 3 results in one column rather than 3 distinct columns

(SELECT DISTINCT COUNT(dm.pat_id) AS Compliant, dep.department_name 
FROM diab dm 
INNER JOIN table_ser_2 ser2 ON dm.cur_pcp_prov_id = ser2.prov_id
INNER JOIN table_dep dep ON ser2.primary_dept_id = dep.department_id
WHERE diab.hba1c_last <='9'
GROUP BY dep.DEPARTMENT_NAME)
UNION ALL
(SELECT DISTINCT COUNT (dm2.pat_id) AS TotalDiabetics, dep.department_name 
FROM dm_diab dm2
INNER JOIN table_ser_2 ser2 ON dm2.cur_pcp_prov_id = ser2.prov_id
INNER JOIN table_dep dep ON ser2.primary_dept_id = dep.department_id
WHERE dm2.hba1c_last IS NOT NULL
GROUP BY dep.department_name)
UNION ALL
(SELECT DISTINCT COUNT (dm3.pat_id) AS TotalMissing, dep.department_name 
FROM dm_diab dm3
INNER JOIN table_ser_2 ser2 ON dm3.cur_pcp_prov_id = ser2.prov_id
INNER JOIN table_dep dep ON ser2.primary_dept_id = dep.department_id
WHERE dm3.hba1c_last IS NULL
GROUP BY dep.department_name)
ORDER BY dep.DEPARTMENT_NAME

Possible duplicate of [Where clause condition on aggregate functions](http://stackoverflow.com/questions/4662955/where-clause-condition-on-aggregate-functions) — Tab Alleman, Feb 19 '16 at 18:45

score 1 · Answer 1 · edited Feb 19 '16 at 22:48

1

SELECT  COUNT(CASE WHEN diab.hba1c_last <='9'     THEN dm.pat_id END) AS Compliant
       ,COUNT(CASE WHEN dm.hba1c_last IS NOT NULL THEN dm.pat_id END) AS TotalDiabetics
       ,COUNT(CASE WHEN dm.hba1c_last is null     THEN dm.pat_id END) AS TotalMissing
       ,dep.department_name 
FROM diab dm 
     INNER JOIN table_ser_2 ser2 ON dm.cur_pcp_prov_id = ser2.prov_id
     INNER JOIN table_dep dep ON ser2.primary_dept_id = dep.department_id
GROUP BY dep.DEPARTMENT_NAME

edited Feb 19 '16 at 22:48

TT.

15,774
6
47
88

answered Feb 19 '16 at 18:49

M.Ali

67,945
13
101
127

Thank you. The lightbulb went on and I got it and was glad to see confirmation I was right when I saw your answer – agalgay Feb 19 '16 at 19:27

Need to do aggregate function on same column multiple times with different where clauses on each result

1 Answers1