SQL summary row after details

Question

Hi Stackoverflow super stars...

Here's my problem. I have a stored procedure that outputs a pretty straightforward table of grouped and summed values. Typical stuff... company name, number of clients at a company, business rep for the company, annual revenue for the company, etc.

That works fine.

What I need now, is the summary row. Last row of the output should be the sum of number of clients, annual revenue, etc. Kind of the same thing you would do by hitting the autosum button in excel. (Which btw is exactly what we do now.)

My inital guess is to insert all the data into a temp table within the stored procedure and then insert the summary values at the very end prior to spitting out the data.

Thoughts? Is this way too confusing?

Thanks,

Answer 1

Add WITH ROLLUP to the end of your query. This will give you summary rows for every grouping. You can add an extra column using the GROUPING(column) function to determine whether the row is a rollup row or not.

MSDN example:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item                 Color                QtySum                     
-------------------- -------------------- -------------------------- 
Chair                Blue                 101.00                     
Chair                Red                  210.00                     
Chair                ALL                  311.00                     
Table                Blue                 124.00                     
Table                Red                  223.00                     
Table                ALL                  347.00                     
ALL                  ALL                  658.00