Geek Answers Handbook

SQL summary line after details

Hello super stars stackoverflow ...

Here is my problem. I have a stored procedure that outputs a fairly simple table of grouped and summed values. Typical material ... company name, number of customers in the company, sales representative of the company, annual income for the company, etc.

It works great.

Now I need a summary line. The last line of exit should be the sum of the number of customers, annual income, etc. The view of the same action that you would do by clicking the autosum button in excel. (Which by the way is exactly what we are doing now.)

My assumption is to insert all the data in the temporary table into the stored procedure, and then insert the totals at the very end before splashing out the data.

Thoughts? Is it too confusing?

Thanks,

+4
source share
4 answers

Your suggestion in the pace table sounds good.

You may have a stored procedure for returning two separate result sets, but this usually becomes a problem when you need to read data through ADO.NET or something else.

But your own temp table suggestion is what I would go with.

+1
source

Add WITH ROLLUP at the end of your query. This will give you summary lines for each grouping. You can add an extra column using the GROUPING (column) function to determine if the row is a convolution 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
+6
source 
 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 additional row with a NULL field in id and smooths in the corresponding fields.

+1
source

ROLLUP mentioned in other answers is very useful, but ashamed that COMPUTE is out of date.

This gives a separate set of results after the main one, so you do not need to separate the summary on the client.

However, this is still in SQL Server 2008, so it has several years to live yet ...

0
source

More articles:


All Articles