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 […]





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];