Getting the correct sex count from a View
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