SQL SERVER QUERY TO COUNT MALE,FEMALE AND TOTAL EMPLOYEES | GET GENDER WISE EMPLOYEE SUMMARY

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'  FROMtbEmployeeMaster

Output will be as:

Male	Female	Not Assigned	Total Employee
13	8	3	24