SQL server query to group totals

Question

I have a table as below
Emp ID, empname, join date, gender
101/ Steve/ 01-01-2021/male
102/john/23-01-21/male
103/steffy/14-01-21/female

I have to get the totals as below:
JoinYear / JoinMonth/TotalCnt/MaleCnt/ FemaleCnt
2021 / 01 /3 /2 / 1

So far I've managed to get up to the totalcount using below query
Select year(joindate) as joinyr,
month(joindate) as joinmth,
count(*) as totalcount,
0 as malecount,
0 as femalecount
From tblemp
Group by year(joindate), month(joindate)

How to get count of males and females in above query in an efficient way.
Thanks.

Answer 1

One of approaches:

select year(joindate) as joinyr,
   month(joindate) as joinmth,
   count(*) as totalcount,
   count(nullif(gender, 'female')) as malecount,
   count(nullif(gender, 'male')) as femalecount
from tblemp
group by year(joindate), month(joindate)

Answer 2

Hi @Newbie Newbie ,

Welcome to the microsoft TSQL Q&A forum!Your question is related to tsql, so my colleague helped you add the tsql tag.

Please also check:

CREATE TABLE #tblemp([Emp ID] INT, empname VARCHAR(25),joindate DATE,gender VARCHAR(25))  
INSERT INTO #tblemp VALUES  
(101,'Steve','01-01-2021','male'),  
(102,'john','01-23-21','male'),  
(103,'steffy','01-14-21','female')  
  
SELECT year(joindate) as joinyr,  
month(joindate) as joinmth,  
count(*) as totalcount,  
count(iif(gender='male',1,null)) malecount,  
count(iif(gender='female',1,null)) femalecount  
FROM #tblemp  
GROUP BY year(joindate), month(joindate)  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Also check:

select joinyr, joinmth, 
    male + female as totalcount, 
    male as malecount, 
    female as femalecount
from ( select year(joindate) as joinyr, month(joindate) as joinmth, gender from tblemp ) t
pivot ( count(gender) for gender in (male, female) ) p