Calculating Ratio Within Groups using Self Join

Posted: May 21, 2013 by Transformer in MS Access, SQL
Tags: ,

Problem Statement: There is table named Employee that has department wise employee details as given below:


Dept	Employee_ID	Gender
IT	E123	          M
IT	E124	          M
IT	E125	          M
IT	E126	          M
IT	E127	          F
IT	E128	          F
HR	E129	          M
HR	E130	          F
HR	E131	          F
HR	E132	          F
HR	E133	          M

We have to calculate ratio between male and female employees in each department.
Solution: This can be done using a self join query where we calculate gender wise population in one query and department wise population in the other and join them as below:

SELECT T1.Dept, T1.Gender,T1.Pop/T2.Pop
    FROM
            (SELECT Dept,Gender,count(*)  as Pop
                  FROM  
                        Employee  Group By Dept,Gender) as T1

                       INNER JOIN

             (SELECT Dept,Count(*) as Pop
                   FROM
                       Employee  Group By Dept) as T2

 ON T1.Dept = T2.Dept
Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s