Group a column into different subtotals

Question

Group a column into different subtotals

Trybbe

Hall of Fame

Points: 3967
More actions
November 14, 2013 at 12:33 am

#280199

Hi
I know this might be a pretty obvious thing to many. I need help to split data in a column into two categories with subtotals before grouping them into one grand total.
I have data like this :
EmpLevel NumberOfstaff
Snr Mngmnt 6
Jnr Mngmnt 13
Non Mngmnt 30
Non Pmnt 10
Exclusions 5
I need to display it like this:
Snr Mngmnt 6
Jnr Mngmnt 13
Non Mngmnt 30
Sub Total : 49 ----- Sum of all three on top
Non Pmnt 10
Exclusions 5
Grant Total : 64 ----- Sum of subtotal + the two after it.
Please assist

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

WITH cte (MainGroup,EmpLevel,NumberOfstaff) AS (SELECT CASE WHEN EmpLevel LIKE '%Mngmnt' THEN 1 ELSE 2 END,EmpLevel,NumberOfstaff FROM

)

SELECT COALESCE(EmpLevel,CASE WHEN GROUPING(MainGroup) = 0 THEN 'Sub Total' ELSE 'Grand Total' END),SUM(NumberOfstaff) AS [NumberOfstaff]

FROM cte

GROUP BY MainGroup,EmpLevel WITH ROLLUP

HAVING NOT (GROUPING(MainGroup) = 0 AND GROUPING(EmpLevel) = 1 AND MainGroup = 2)

Far away is close at hand in the images of elsewhere.
Anon.

Tee Time Hall of Fame Points: 3745 More actions · Answer 2

I would do this with a CASE statment in the SQL query, something like:

CASE WHEN EmpLevel IN ('Non Pmnt', 'Exclusions') THEN 'Temp' ELSE 'Regular' END AS EmployeeType

and then create a group in your report, using the EmployeeType field.

Trybbe Hall of Fame Points: 3967 More actions · Answer 3

Thanks a mil guys for your help. both your answers helped me a lot both on reporting services and on query side as well. I can even use this method on other reporting tools.