Calculating Subtotals Within Groups of Data Using UNION

[Transformer]

UsefulGyaan Has Posted the Following On 05-24-2013 10:04 AM:

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 […]http://stats.wordpress.com/b.gif?host=usefulgyaan.wordpress.com&blog=50473542 &post=777&subd=usefulgyaan&ref=&feed=1





Calculating Subtotals Within Groups of Data Using UNION (http://usefulgyaan.wordpress.com/2013/05/24/calculating-subtotals-within-groups-of-data-using-union/)

[Excel Fox]

The sub-totals appear below each group only because "T...." is larger than "M" or "F". For the sake of the purists, adding an order by would be more apt.


SELECT [CUSTABLE].[Dept], [CUSTABLE].[Gender], [CUSTABLE].[Pop]
FROM
(SELECT [Dept], 1 As [Order], [Gender], Count(*) as Pop
FROM [Employee] GROUP BY [Dept], [Gender]
UNION SELECT [Dept], 2 As [Order], "Total", Count(*) as Pop
FROM [Employee] GROUP BY [Dept]) CUSTABLE
ORDER BY [CUSTABLE].[Dept], [CUSTABLE].[Order];