CODEWITHSUNDEEP
sql-serverdistinctcase-when
KLR
KLR

Reputation: 61

Distinct Count when using Case When

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.

Upvotes: 1

Views: 892

Answers (2)

digital.aaron
digital.aaron

Reputation: 5707

You can also use a SUM() for the second two fields, like this:

SELECT
    Count = COUNT(DISTINCT TA_STUDENT_ID)
    ,CCC = SUM(CASE WHEN ( TA_LOCATION = 'CCC' ) THEN 1 ELSE 0 END)
    ,SCCDC = SUM(CASE WHEN ( TA_LOCATION = 'SCCDC' ) THEN 1 ELSE 0 END)
    ,TA_AW_ID
FROM S85_TA_ACYR
GROUP BY TA_AW_ID
ORDER BY TA_AW_ID

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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

Upvotes: 1

Related Questions