cumillative total

Question

Post reply

cumillative total

Soft Developer

SSC Journeyman

Points: 83
More actions
October 5, 2011 at 5:37 am

#246075

Hi all,
I had a question....
create table #t(id int, dt datetime, amt float)
insert into #t select 1,dateadd(d,-2,getdate()),100 union
select 1,dateadd(d,-1,getdate()),150 union select 1,getdate(),250 union
select 2,dateadd(d,-3,getdate()),100 union select 2,dateadd(d,-2,getdate()),150 union
select 2,getdate(),250
select * from #t
iddt amt
110/3/2011100
110/4/2011150
110/5/2011250
210/2/2011100
210/3/2011150
210/5/2011250
Can I write a query to get the amt column showing cumilative total for each id as shown below...
iddt amt
110/3/2011100
110/4/2011250
110/5/2011500
210/2/2011100
210/3/2011250
210/5/2011500
Please help me in this doubt.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Hi ,

Running totals can be solved but are quite inefficient,

You have 3 options :

Triangular join ( Very Slow)

Quirky Update ( Lot of hoops to jump through)

Cursor (blurgh and slow)

In Denali its a simple one-liner 🙂

Clear Sky SQL
My Blog[/url]

tommey152 Right there with Babe Points: 772 More actions · Answer 2

Soft Developer (10/5/2011)
Hi all,
I had a question....
create table #t(id int, dt datetime, amt float)
insert into #t select 1,dateadd(d,-2,getdate()),100 union
select 1,dateadd(d,-1,getdate()),150 union select 1,getdate(),250 union
select 2,dateadd(d,-3,getdate()),100 union select 2,dateadd(d,-2,getdate()),150 union
select 2,getdate(),250
Please help me in this doubt.

Hi

Same like ur quary refer to the below link:

http://www.sqlservercentral.com/Forums/Topic1137108-391-1.aspx?Highlight=running+total

_______________________________________________________________

Need help? Help us help you.

vince_sql Right there with Babe Points: 722 More actions · Answer 3

You can do cumulative totals using a correlated subquery.

SELECT id,dt,amt,(SELECT SUM(amt) FROM #t b WHERE a.id = b.id AND a.dt >= b.dt)

FROM #t a

http://sqlvince.blogspot.com/[/url]

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

vince_sql (10/6/2011)
You can do cumulative totals using a correlated subquery.
SELECT id,dt,amt,(SELECT SUM(amt) FROM #t b WHERE a.id = b.id AND a.dt >= b.dt)
FROM #t a

I would very much advise against that , for all but the smallest amount of rows.

Clear Sky SQL
My Blog[/url]

Scott Murray-240410 SSCarpal Tunnel Points: 4649 More actions · Answer 5

Any chance you are using a reporting tool which will do the rollup / running total?

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 6

I guess the question is what are you going to do with this query? Is this to be kept as a persisted column in a table? Is this going to be displayed in a report? Is this just for a client interface?

What type of business are you doing that requires this running total? A bank that is going to have thousands of customers looking at their balances every hour (individualized running totals)? An insurance company where an analyst just needs a dashboard once a week of claims and premiums (summary running totals)? An inventory warehouse or airline company where you need to know for all stores how many 9 penny nails are in stock or how many tickets have been sold for a certain flight (the middle ground)?

The reason I ask this question is this will inform your solution. You need to know how many potential users are hitting this query, how often, and how many times it will be run. A bank will use the running totals query much more often than an insurance analyst and the inventory / airline will too, but probably use it less often than a bank because the items are grouped in bigger sets.

Once you know this information, you know your latency / performance requirements. Then we can help you find a better solution.

Though, if you're trying to solve the running totals puzzle that was recently posted elsewhere (and tends to be a homework assignment), asking the question of others here is kind of a cheat.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 7

Dave Ballantyne (10/6/2011)
vince_sql (10/6/2011)
You can do cumulative totals using a correlated subquery.
SELECT id,dt,amt,(SELECT SUM(amt) FROM #t b WHERE a.id = b.id AND a.dt >= b.dt)
FROM #t a
I would very much advise against that , for all but the smallest amount of rows.

I completely agree with Dave on this. If you do this type of triangular join on a small number of rows such as the original posted table, it looks very fast. Try some testing by gradually increasing the number of rows in the table. You'll find that when the tipping point is hit, performance degrades very quickly.

I used the correlated sub-query technique years ago when working on an international film box office reporting system. When the number of films per country hit around 45 films, the performance plunged dramatically. That's a very small number of rows. We of course had to change the way we were doing things.

Todd Fifield