Getting the correct sex count from a View

Question

I have a view results like this:

Teacher_id	Sex	Missed_Day
45	m	19-01-2023
37	f	19-01-2023
45	m	01-02-2023
78	m	01-02-2023

I would like a SELECT count(Sex) to give me results as follows as the Teacher_id = 45 is a duplicate so it is one male not two:

Male	Female
2	1

Currently I am having this :

COUNT(DISTINCT CASE WHEN Sex = 'm' THEN 1 END) AS Male, 
COUNT(CASE WHEN Sex = 'f' THEN 1 END) AS Female

Male	Female
3	1

How do I write this query to give me the desired results by not counting sex twice of Teacher_id

Answer 1

You need to pregroup your view first:

select count(case when sex = 'm' then 1 end) AS male
, count(case when sex = 'f' then 1 end) AS female
,count(case when sex not in ('m', 'f') then 1 end) AS other
from (
  select distinct sex, teacher_id
  from yourview
) x

EDIT: If you want to combine regular COUNT with count distinct, you can use the following construct:

select  COUNT(DISTINCT CASE WHEN Sex = 'm' THEN Teacher_id END) AS m
,   COUNT(DISTINCT CASE WHEN Sex = 'f' THEN Teacher_id END) AS f
,   COUNT(DISTINCT CASE WHEN Sex not in ('f', 'm') THEN Teacher_id END) AS other
,   COUNT(*)
from (
    VALUES  (45, N'm', N'19-01-2023')
    ,   (37, N'f', N'19-01-2023')
    ,   (45, N'm', N'01-02-2023')
    ,   (78, N'm', N'01-02-2023')
) t (Teacher_id,Sex,Missed_Day)

This allows keeping duplicates but not counting them for some of the fields. COUNT DISTINCT has some performance implications, but for smaller datasets, it should be fine