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)