Show a sum of the column at the end of the column in mssql 2008

Question

In MsSQL 2008, I am trying to show the sum of 3 different columns at their end.

Currently my query returns

this is selected by (@grouping = maingroup,subgroup,subsubgroup,season,vendorid,[01] as '01',[02] as '02',Total)

set @sql = 'select '+@grouping+' from ##aa'
execute sp_executesql @sql

I need the sum of 01,02 and Total at the end of their respective columns. I have tried using rollup but that doesn't give me the correct out come. Probably just doing it wrong, but what would the select statement have to look like to get what I need?

Answer 1

Two ways you can do this. The first is the old pre sql-server-2008 way to do this would be to use a UNION ALL with the first SELECT returning the original data from the table and the second returning SUMS of the 01, 02 and Total fields:

SELECT   maingroup,
         subgroup,
         subsubgroup,
         season,
         vendorid,
         [01] AS '01',
         [02] AS '02',
         Total AS Total
FROM     #aa
UNION ALL
SELECT   null as maingroup,
         null as subgroup,
         null as subsubgroup,
         null as season,
         null as vendorid,
         sum([01]) AS '01',
         sum([02]) AS '02',
         sum(Total) AS Total
FROM     #aa

In sql-server-2008 and later you can use instead use GROUPING SETS for this. The equivlant query to the one above using GROUPING SETS is:

SELECT   maingroup,
         subgroup,
         subsubgroup,
         season,
         vendorid,
         sum([01]) AS '01',
         sum([02]) AS '02',
         sum(Total) AS Total
FROM     #aa
GROUP BY GROUPING SETS((maingroup, subgroup, subsubgroup, season, vendorid), ())

Both should return:

GROUPING SETS Equivalents and Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS are two additional sources with good information on using GROUPING SETS