Distinct Count when using Case When

Question

I am having trouble getting the correct distinct count when using case when and distinct in SQL Server.

I have a column for count(distinct TA_STUDENT_ID) which calculates the correct count. I then have 2 additional columns in which I am doing a count by TA_LOCATION and the totals for the numbers on each row under TA_LOCATION need to add up to the COUNT of the DISTINCT TA_STUDENT_ID. I need help to get it to do that. Here is my query:

SELECT 
count  (distinct TA_STUDENT_ID) as 'count',
COUNT (CASE WHEN (TA_LOCATION = 'CCC') THEN 'CCC'  END) AS 'CCC',
COUNT(CASE WHEN (TA_LOCATION = 'SCCDC') THEN 'SCCDC'  END) AS 'SCCDC',      
[TA_AW_ID]
FROM  [S85_TA_ACYR]
group by   [TA_AW_ID]
order by  [TA_AW_ID]

My desired results are that the totals on each row in the TA_LOCATION columns should total and match the numbers on the rows in my COUNT DISTINCT TA_STUDENT_ID column.

Answer 1

Use DISTINCT inside COUNT() with CASE expression :

SELECT COUNT(DISTINCT TA_STUDENT_ID) as 'count',
       COUNT(DISTINCT CASE WHEN (TA_LOCATION = 'CCC') THEN TA_STUDENT_ID  END) AS 'CCC',
       COUNT(DISTINCT CASE WHEN (TA_LOCATION = 'SCCDC') THEN TA_STUDENT_ID  END) AS 'SCCDC',      
       [TA_AW_ID]
FROM  [S85_TA_ACYR]
GROUP BY [TA_AW_ID]
ORDER BY [TA_AW_ID];