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

Introduction: In this article example I am going to share sql sever query to get department wise staff gender summary i.e. counting  male, female and total number of employees in each department.

In previous articles i explained How to count male,female and total employees and Case when example in select statement in sql and Copy all data from one table to another in the same database and Stored procedures for insert,update,delete,bind and search operation in table and Convert sql server 2012,2008 database to lower version and Use of Cursor in SQL SERVER with simple example

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 Employees' 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 BY Department
) AS TB
ORDER BY CASE WHEN TB.Department IS NULL THEN 1 ELSE 0 END

 Output will be as:

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

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