Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mufasa
Yak Posting Veteran
62 Posts |
 Posted - 2004-05-31 : 16:12:01
|
Hi thereI am looking for a way to get a running sum.I have records seperated by month,year,storeplus numeric values, transactions & customers.I would like a query to show me the total customers to date plus the amount of customers for that month in the same line.ie.
year mm str trn ncust ttlcust2004 01 151 600 167 1672004 02 151 435 200 3672004 03 151 562 187 554 Thanks for any help Mufasa |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-31 : 16:15:57
|
| select *, ttlcust = (select sum(ncust) from tbl t2 where t2.str = t1.str and t2.year*100+t2.mm <= t1.year*100+t1.mm)from tbl t1order by ...==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mufasa
Yak Posting Veteran
62 Posts |
Posted - 2004-05-31 : 17:13:02
|
| Thanks works great, did not quite understand your formula t1 and t2 but I did get it to work with the following formula:select *, ttlcust = (select sum([New Customers]) from CutomerBM where CutomerBM.Store = dbo.CutomerBM.Store and CutomerBM.[Year]*100+CutomerBM.[Month] <= dbo.CutomerBM.[Year]*100+dbo.CutomerBM.[Month]) FROM dbo.CutomerBMThanks againMufasa |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-31 : 19:27:59
|
| t1 and t2 are aliases for the tableselect *, ttlcust = (select sum(t2.[New Customers]) from CutomerBM t2where t2.Store = t1.Store and t2.[Year]*100+t2.[Month] <= t1.[Year]*100+t1.[Month])FROM dbo.CutomerBM t1==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Running Sums