Calculating Subtotals Within Groups of Data Using UNION

Posted: May 24, 2013 by MaxFraudy in MS Access, SQL
Tags:

Problem Statement: There is a table named Employee which has department wise employee details as given below. We want to find out gender-wise subtotals and a grand total for each department.


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

Solution: This can be done using a UNION query where we calculate gender wise population in one subquery and department wise population in the other and then union them as below:


SELECT [Dept], [Gender], count(*)  as Pop
FROM  
        [Employee]  
GROUP BY [Dept], [Gender]

UNION                   

SELECT [Dept], "Total", Count(*) as Pop
FROM
        [Employee]  
GROUP BY [Dept]

This query will return the following result:

Dept	    Gender	 Pop
HR	       F	   3
HR	       M	   2
HR	    Total	   5
IT	       F	   2
IT	       M	   4
IT	    Total	   6
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