IF OBJECT_ID('tempdb..#TMP2') IS NOT NULL
BEGIN
drop table #TMP2
END
SELECT CASE GROUPING([departmant])
WHEN 1 THEN 'Total'
ELSE [departmant] END AS 'departmant',
CASE GROUPING([achead])
WHEN 1 THEN 'Total'
ELSE [achead] END AS 'achead',
COUNT([location]) AS 'Total_Count_Location' INTO #TMP2
FROM temptest
GROUP BY [departmant], [achead] WITH ROLLUP
SELECT CASE achead WHEN 'Total' then '' else departmant end as departmant,
achead,
Total_Count_Location
FROM #TMP2 WHERE departmant!='TOTAL'
Try The Above code. If u have a simpler solution Plz share Gud Luck