CODEWITHSUNDEEP
sql-server
agalgay
agalgay

Reputation: 13

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

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

Upvotes: 0

Views: 277

Answers (1)

M.Ali
M.Ali

Reputation: 69514

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

Upvotes: 1

Related Questions