question

Kerry avatar image
Kerry asked

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                    
sql-server-2005aggregatessub-total
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kristen avatar image
Kristen answered

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]            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.