Sql-server – sql server getting total for each group

group bysql server

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

Best Answer

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