You can also use the sub-clause GROUPING SETS to specifiy the groups. Use function GROUPING to sort the data as desired (Total at the end).

USE tempdb;

GO

SELECT

GroupName, NumberOfCases

INTO #Temp

FROM

(

VALUES

('Grp A',10),

('Grp B',20)

) AS T(GroupName,NumberOfCases);

SELECT

CASE WHEN GROUPING(GroupName) = 1 THEN 'Total' ELSE GroupName END as GroupName,

SUM(NumberOfCases) AS sum_NumberOfCases

FROM

#Temp

GROUP BY

GROUPING SETS (

(GroupName),

()

)

ORDER BY

GROUPING(GroupName),

GroupName;

DROP TABLE #Temp;