Calculate Total Sum in SQL Stored Procedure

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



LVL 1
SasDevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SasDevAuthor 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
0
BrandonGalderisiCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SasDevAuthor Commented:
Yes this is for an AD-HOC query. Your solution worked perfectly! Thanks BrandonGalderisi!
-- Traci
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

SasDevAuthor Commented:
Thanks again!
0
BrandonGalderisiCommented:
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.
0
SasDevAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.