Please disable your adblock and script blockers to view this page
 

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.


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 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 Employee
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."
Previous
Next Post »

1 comments:

Click here for comments
Anonymous
admin
June 07, 2015 ×

nice one

Congrats bro Anonymous you got PERTAMAX...! hehehehe...
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.. ConversionConversion EmoticonEmoticon