Sql Server query to count male,female and total employees | Get gender wise employee summary

Introduction: In this article example I am going to share the queries to get employee data gender wise i.e. counting male, female and total number of employees.


Description: It is very common requirement to get and show employee data gender wise.There are numerous ways to get this done but i have mentioned two queries to count the following:
  • Total number of male employees
  • Total number of female employees
  • All the employees where gender value is not assigned.
  • Total number of employees 

Implementation: Let’s create a table 'tbEmployeeMaster' and insert some data into it for demonstration purpose using the below script:

CREATE TABLE tbEmployeeMaster
(
                EmployeeId                       INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                EmployeeName                NVARCHAR(50),
                Gender                                NVARCHAR(10),
                Department                       NVARCHAR(50)
)

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Arjun','Male','Administration')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Rohan','Male','Sales')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Ishita',NULL,'HRM')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Aadi','Male','Sales')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Preetam','Male','HRM')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Anjan','Male','Administration')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Rajesh',NULL,'HRM')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Ankur','Male','HRM')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Robin','Male','HRM')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Mayank','Male','Sales')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Manisha','Female','HRM')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Sonam','Female','HRM')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Rajan','Male','HRM')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Kapil',NULL,'Sales')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Ritika','Female','HRM')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Akshay','Male','Finance')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Aryan','Male','HRM')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Anju','Female','Finance')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Sapna','Female','Finance')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Ankur','Male','Sales')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Robin','Male','Sales')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Neelam','Female','HRM')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Rajni','Female','Administration')

INSERT INTO tbEmployeeMaster(EmployeeName,Gender,Department) VALUES ('Sonakshi','Female','Finance')

--Check data in table
SELECT *  FROM tbEmployeeMaster

--Get gender wise employee summary in rows
SELECT ISNULL(Gender,'Not Assigned') AS Gender, COUNT(EmployeeId) AS 'Total Employee' FROM tbEmployeeMaster
GROUP BY Gender ORDER BY CASE WHEN Gender IS NULL THEN 1 ELSE 0 END

Output will be as: 
Gender
Total Employee
Female
8
Male
13
Not Assigned
3

Note: Have you noticed the ORDER BY Clause in the above query? It actually places all the 'Not Assigned' genders at last.

--Get gender wise employee summary in columns using SUM and COUNT functions
SELECT SUM(CASE WHEN UPPER(Gender) = 'MALE' THEN 1 ELSE 0 END) AS Male,
SUM(CASE WHEN UPPER(Gender) = 'FEMALE' THEN 1 ELSE 0 END) AS Female,
SUM(CASE WHEN Gender IS NULL  THEN 1 ELSE 0 END) AS 'Not Assigned',
COUNT(EmployeeId) AS 'Total Employee'  FROM tbEmployeeMaster


--Get gender wise employee summary in columns using just COUNT functions
SELECT COUNT(CASE WHEN UPPER(Gender) = 'MALE' THEN 1 END) Male,
COUNT(CASE WHEN UPPER(Gender) = 'FEMALE' THEN 1 END) Female,
COUNT(CASE WHEN Gender IS NULL THEN 1 END) 'Not Assigned',COUNT(EmployeeId) AS 'Total Employee'  FROM tbEmployeeMaster

Output will be as:
Male
Female
Not Assigned
Total Employee
13
8
3
24
Now over to you:
"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

4 comments

Click here for comments
SQL Tips
admin
December 23, 2017 ×

good explanation.....keep going on..........

Reply
avatar
Anonymous
admin
June 29, 2019 ×

Thats what I was looking for.
Many thanks!

Reply
avatar
February 24, 2020 ×

You gave explanation very nicely.. Easy to understand even for beginners keep going on. I would like to see more posts on tricky sqls

Reply
avatar
January 08, 2022 ×

good , keep it up and 1 interviewer asked me this question.
Thanks and god bless you ..

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..