x

How to get subtotals and totals in query

I have a query that reads multiple tables and outputs a file that is used in excel for reporting. The users have now requested to add subtotals to the file. I have never done this in SQL, and not sure how to do it.

Here is what my table looks like.

                    
Dept,Vendor,POA,,Vendor Name,Sku,Sku Desc,Store Disp,Quantity,Ext Cost,                    
AIS Def Allow,YTD Volume,YTD Collected Def Allow,Pct Return to Purchases                    

And the output is a tab delimited file, with all of the fields. This is what the file looks like now...

                    
1 10015 1 Waters 12345678 WaterInc DC 25 12.50 0 361919.80 0 .003                    
1 10015 1 Waters 12345678 WaterInc DC 31 36.50 0 361919.80 0 .010                    
1 10024 1 Candy  12345692 CandyInc DC 14 78.00 0  29166.00 0 .002                    
1 10024 1 Candy  12345692 CandyInc DC 06 14.80 0  29166.00 0 .005                    

Would like for it to look like...

                    
1 10015 1 Waters 12345678 WaterInc DC 25 12.50 0 361919.80 0 .003                    
1 10015 1 Waters 12345678 WaterInc DC 31 36.50 0 361919.80 0 .010                    
                                      56 49.00   361919.80   .013                      
1 10024 1 Candy  12345692 CandyInc DC 14 78.00 0  29166.00 0 .002                    
1 10024 1 Candy  12345692 CandyInc DC 06 14.80 0  29166.00 0 .005                    
                                      56 92.80    29166.00   .300                      

Query similiar to this...


select 1_store_dept Dept,
1_store_vendor Vendor,
2_poa POA,
2_poaname Vendor_Name,
1_store_sku Sku,
3_itemdesc1 Description,
1_store_disp Store_Disp,
sum(1_store_qty) Qty,
sum(1_store_qty*1_store_cost) Cost,
(2_def_allow_pct*.01) Def_Allow,
b.YTD_Total_Cost_Vendor Vendor_YTD_Volume,
ISNULL(a.amount,0) YTD_Col_Def_Allow,
case when b.YTD_Total_Cost_Vendor =0 then 0
else (sum(1_store_qty*1_store_cost)/b.YTD_Total_Cost_Vendor)*100
end Pct_Returns_to_Purchase
from 1
left outer join 3_item_master on 1_store_sku = 3_item
left outer join 2_vendor_poa_master on 3_vendno = 2_vendno and 3_poa=2_poa left outer join (select 4_dept dept,
4_vendor vendor,
sum(4_amount) amount
from 4_defective_allowances
where 4_dept between 1 and 38
group by 4_dept, 4_vendor) a on a.dept = 1_store_dept and a.vendor = 2_vendno
left outer join (select 4_dept dept,
cast(left(4_vendor,charindex(' -',4_vendor))as int) vendor,
ISNULL(sum(4_ty_total_cost),0) YTD_Total_Cost_Vendor
from 4_vendor_volume_report
where 4_year = @fyear
group by 4_dept, 4_vendor) b on b.dept = 1_store_dept and b.vendor = 2_vendno
where 1_store_vendor = t21_vendno
and 1_store_disp <> 'MK'
and 1_store_qty > 0
or 1_store_qty < 0
and 1_store_dept between 1 and 38
and 1_store_scan_date between @begin_date and @end_date
and 1_store_dept = b.dept
group by 1_store_dept,
1_store_vendor,
2_poa,
2_poaname,
1_store_sku,
3_itemdesc1, 1_store_disp, 2_def_allow_pct,
b.YTD_Total_Cost_Vendor,
a.amount,
b.vendor
order by dept, vendor, poa, sku
more ▼

asked Dec 09, 2009 at 06:02 PM in Default

Kerry gravatar image

Kerry
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Three ways:

You could UNION ALL a second query that output dept, vendor, poa, sku and the totals - so this would add the Sub-Total rows. You'd have to be a bit careful with how the results sorted - you could add a "Section" column to the SELECT - value 1 for the main query, 2 for the sub-total query, and add [Section] to your ORDER BY

Or you could use COMPUTE BY (something like COMPUTER SUM(Qty), SUM(Cost), SUM(...) BY dept, vendor, poa, sku - problem with this is that it returns masses of resultsets to the application, whch may cause an issue.

Or you could use ROLLUP, but in my experience you then need to get a little creative with the rows that ROLLUP adds into the resultset for them to be presented in a user-friendly way - usually you wind up having a CASE statement on the ORDER BY columns

EDIT Examples:


SELECT *
INTO #TEMP
FROM
(
SELECT [Dept] = 1,
[Vendor] = 10015,
[POA] = 1,
[Vendor Name] = 'Waters',
[Sku] = 12345678,
[Sku Desc] = 'WaterInc',
[Store Disp] = 'DC',
[Quantity] = 25,
[Ext Cost] = 12.50,
[AIS Def Allow] = 0,
[YTD Volume] = 361919.80,
[YTD Collected Def Allow] = 0,
[Pct Return to Purchases] = 0.003
UNION ALL
SELECT 1, 10015, 1, 'Waters', 12345678, 'WaterInc', 'DC', 31, 36.50, 0,
361919.80, 0, .010 UNION ALL
SELECT 1, 10024, 1, 'Candy', 12345692, 'CandyInc', 'DC', 14, 78.00, 0,
29166.00, 0, .002 UNION ALL
SELECT 1, 10024, 1, 'Candy', 12345692, 'CandyInc', 'DC', 06, 14.80, 0,
29166.00, 0, .005
) AS X

SELECT
[Section] = 1,
Dept,Vendor,POA,[Vendor Name],Sku,[Sku Desc],[Store Disp],
Quantity,[Ext Cost],[AIS Def Allow],[YTD Volume],
[YTD Collected Def Allow],[Pct Return to Purchases]
FROM #TEMP
UNION ALL
SELECT
[Section] = 2,
Dept,Vendor,POA,'**TOTAL**',Sku,[Sku Desc],[Store Disp],
SUM(Quantity),SUM([Ext Cost]),NULL,SUM([YTD Volume]),
NULL,SUM([Pct Return to Purchases])
FROM #TEMP
GROUP BY Dept,Vendor,POA,[Vendor Name],Sku,[Sku Desc],[Store Disp]
ORDER BY Dept,Vendor,POA,Sku,[Sku Desc],[Store Disp], [Section]

SELECT
Dept,Vendor,POA,[Vendor Name],Sku,[Sku Desc],[Store Disp],
Quantity,[Ext Cost],[AIS Def Allow],[YTD Volume],
[YTD Collected Def Allow],[Pct Return to Purchases]
FROM #TEMP
ORDER BY Dept,Vendor,POA,[Vendor Name],Sku,[Sku Desc],[Store Disp]
COMPUTE SUM(Quantity),SUM([Ext Cost]),SUM([YTD Volume]),
SUM([Pct Return to Purchases])
BY Dept,Vendor,POA,[Vendor Name],Sku,[Sku Desc],[Store Disp]

SELECT
Dept,Vendor,POA,[Vendor Name],Sku,[Sku Desc],[Store Disp],
SUM(Quantity),SUM([Ext Cost]),MIN([AIS Def Allow]),SUM([YTD Volume]),
MIN([YTD Collected Def Allow]),SUM([Pct Return to Purchases])
FROM #TEMP
GROUP BY Dept,Vendor,POA,[Vendor Name],Sku,[Sku Desc],[Store Disp]
WITH ROLLUP
ORDER BY Dept,Vendor,POA,Sku,[Sku Desc],[Store Disp]
more ▼

answered Dec 10, 2009 at 05:10 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

Don't quite understand how to get the subtotals in there. Can you provide an example?
Dec 10, 2009 at 12:29 PM Kerry
Using the union all example
Dec 10, 2009 at 12:30 PM Kerry
I've added examples to my answer
Dec 11, 2009 at 06:07 AM Kristen ♦
Got it to work. Thanks Kristen!
Dec 16, 2009 at 04:58 PM Kerry
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1947
x58
x3

asked: Dec 09, 2009 at 06:02 PM

Seen: 3554 times

Last Updated: Dec 10, 2009 at 04:57 AM