Printable View
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?hos...f=&feed=1
Calculating Subtotals Within Groups of Data Using UNION
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.
PHP Code: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];