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.

In previous articles I explained How to count department wise male,female and total employees and  split comma separated string into table rows in Sql server and Using CTE to remove duplicate records from table and Try Catch to handle exception and Commit,Rollback transaction in sql server and Difference between Primary Key and Foreign Key and Self Join with example in sql server 

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."

• Tweet
• Share
• Share
• Share

Related Post