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

Description: There are numerous ways to get this done but here i have mentioned the query to count the following:

• Total number of male employees in each department
• Total number of female employees in each department
• All the male and female employee and their total where department is not assigned.
• Total number of employees in each department 

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',NULL)

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 ('Ruhi','Female',NULL)

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 department wise male, female and total employees in each department

SELECT ISNULL(TB.Department,'Not Assigned') AS Department, TB.Male, TB.Female, (TB.Male + TB.Female) AS 'Total Employee' FROM(SELECT Department  ,

COUNT(CASE WHEN UPPER(Gender)='MALE' THEN 1 END) AS Male,

COUNT(CASE WHEN UPPER(Gender)='FEMALE' THEN 1 END)AS Female FROM tbEmployeeMaster GROUP BYDepartment) AS TB  ORDER BY CASE WHEN TB.Department IS NULL THEN 1 ELSE 0 END

 Output will be as:

Department	Male	Female	Total Employee
Administration	2	1	3
Finance	1	3	4
HRM	4	4	8
Sales	4	0	4
Not Assigned	1	1	2