We help IT Professionals succeed at work.

Calculate Total Sum in SQL Stored Procedure

SasDev
SasDev asked
on
I would like to have a new field that showes the total sum of netsales for all of the records specified in my criteria. Is there a simple way to do this?
Here is what I have so far:
ALTER PROCEDURE spSalesAnalysis
--Exec spSalesAnalysis 2559,2921,0
(
@BegDate as int,
@EndDate as int,
@SalesRep as smallint
)


AS

SELECT c.datenk as fiscaldate, c.fiscalweek, a.salesrepwk, b.salesrepname, b.salesregion, b.salesregionname as regionname,
d.customername as customer, a.salesdatewk, a.customerwk, a.invoice, a.invoiceline, a.itemwk, a.fiscaldatewk, a.unitofissue, a.quantityordered,
a.quantitysold, a.extendedprice, a.extendedpromotions, a.marketingallowances, a.deposits, a.salestax, a.freightservicefees,
a.telxonfees, a.othercharges, a.extendedretail, a.extendedcost, a.costallowances, a.shippingwarehouse, a.customcategorycode, a.customcategorystyle,
a.customcategorydesc, a.source, a.internalcost, a.buyercost, a.creditinvoiceflag, a.commission, a.itemtax,


case when a.salesDateWK between @BegDate and @EndDate then a.extendedprice - a.extendedpromotions + a.Marketingallowances +
a.deposits + a.salestax + a.freightservicefees + a.telxonfees + a.othercharges end as netsales

into #tempSales

from factcustsales a
INNER JOIN DimSalesRep b
     ON a.SalesRepWK = b.SalesRepWK
INNER JOIN DimCalendar c
     ON a.salesdatewk  = c.DateWK
INNER JOIN DimCustomer d
     ON a.CustomerWK = d.CustomerWK

WHERE  
a.salesrepwk = b.salesrepwk and
b.salesregion in ('4') and
d.warehousetransfer = ('No') and
a.creditinvoiceflag <> ('RB')and
a.salesdateWK between @BegDate and @EndDate

select  distinct fiscaldate, invoice, customer, salesrepwk, salesrepname, sum(netsales)as netsales
from #tempsales

group by Salesrepname, fiscaldate, customer, invoice, salesrepwk


order by invoice desc



Comment
Watch Question

Author

Commented:
Sample record set:
fiscaldate                 invoice     customer                         salesrepwk      salesrepname          netsales
10/25/2007 0:00      683915      SUNOCO 16 & GROESBECK      45      DAVID NEIRINCK      108.2
10/26/2007 0:00      683914      SUNOCO Z & L                     127      JOE HASTINGS       108.2
10/24/2007 0:00      683913      SUNOCO GARFIELD                      54            CONNIE ELLSTROM        101.2
10/25/2007 0:00      683912      SUNOCO-DHS ENT., INC.      74      LINDA HUTTON      118.2
10/24/2007 0:00      683911      SUNOCO BADR                     378                KAREN J. KLEPETSANIS      99.2
10/25/2007 0:00      683910      SUNOCO TRACTOR INVESTMENT  127      JOE HASTINGS      108.2
So you want the data you have above.... plus an additional display of netsales accumulated?  Is this an ADHOC stored procedure call where the data is viewed on screen or is it pulled as a recordset into some application?  

You should be able to do a compute sum(netsales)
select * from (select  distinct fiscaldate, invoice, customer, salesrepwk,    salesrepname, sum(netsales)as netsales
   from #tempsales  
   group by Salesrepname, fiscaldate, customer, invoice, salesrepwk) a
compute sum(netsales)

Open in new window

Author

Commented:
Yes this is for an AD-HOC query. Your solution worked perfectly! Thanks BrandonGalderisi!
-- Traci

Author

Commented:
Thanks again!
For clarification, the reason I asked if it was for an AD HOC query was because if it were being called from an application, you have to ensure that the data provider you were using allowed you to pull multiple recordsets from the connection at once.  Because the compute PROBABLY would have been returned in a separate recordset.

Author

Commented:
I see...thanks for the heads up.
I was using this sp to compare against similar sales reports we have pulling from a different data source (i5). This will not be used in an application.

Thanks for helping us beginners --

-- Traci