sql server getting total for each group
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