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                     

Answer 2

Your temp table suggestion sounds pretty good.

You could have the Stored Procedure return two separate result sets, but that generally becomes a hassle when you need to read the data via ADO.NET or whatever.

But your own suggestion of the temp table is the one that I would go with.

Answer 3

WITH    data AS
        (
        SELECT  1 AS id, 1 AS value
        UNION ALL
        SELECT  1 AS id, 2 AS value
        UNION ALL
        SELECT  2 AS id, 5 AS value
        )
SELECT  id, COUNT(*) AS cnt, AVG(VALUE) AS a
FROM    data
GROUP BY id WITH ROLLUP

This query will return an extra row with NULL in id field and superaggregates in the corresponding fields.

Answer 4

ROLLUP mentioned in other answers is very useful, but it's a shame that COMPUTE is deprecated.

This gives a separate result set after the main one so you don't have to separate the summary on the client.

That said, it's still in SQL Server 2008 so it has some years to live yet...