sql server getting total for each group

Question

Not sure how to go about this:-

I have a query

select name, city, salary from Employee

I can get a sum by doing a grouping

select name, city, sum(salary) total_salary from Employee group by name, city

How can I get the total per city as a separate row under each city ?

Expected Sample Result:

Name City Salary
n1   c1   10
n2   c1   20
T1   c1   30
n3   c2   20
n4   c2   50
T2   c2   70

Answer 1

You can use GROUP BY with the GROUPING SETS () modifier:

select name, city, sum(salary) total_salary 
from Employee 
group by grouping sets ((city, name), (city)) ;

Example of use from Microsoft Technet: Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

Answer 2

WITH ROLLUP would come close to what you want:

select name, city, sum(salary) total_salary
  from Employee
 group by city, name WITH ROLLUP

This would give you:

Name City Salary
n1   c1   10
n2   c1   20
NULL c1   30
n1   c2   20
n2   c2   50
NULL c2   70
NULL NULL 100

If you don't want the NULLs, you can replace them. The GROUPING function tells you when a column is NULL because of the ROLLUP;

select CASE WHEN GROUPING(city) = 1 THEN 'Grand'
            WHEN GROUPING(name) = 1 THEN 'Total'
            ELSE name END as name
      ,CASE WHEN GROUPING(city) = 1 THEN 'Total'
            ELSE city END as city
      ,sum(salary) total_salary
  from Employee
 group by city, name WITH ROLLUP

See the results in this SQLFiddle