running total - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

running total

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-12 : 09:50:00

Bill John writes "I assume that I have a table as you see in below.

day sales
1 80
2 120
3 120
4 40

and I want to get cumulative values from table

day sales cumu_total
1 80 80
2 120 200
3 120 320
4 40 360

I wrote sql and tried to get cumulative values but My problem is in my result I get the 200 and 200 values instead of 200 and 320 in cumulative total.In fact my table is much more complex.
Do you have any idea ?
"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-12 : 09:54:24

http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-12 : 09:58:40

Where do you want to show the data?
If you use Reports, make use of its Running Total feature

Madhivanan

Failing to plan is Planning to fail

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-12 : 23:01:55

DROP TABLE TEMPDB
GO

CREATE TABLE TEMPDB(
D INT IDENTITY(1,1) NOT NULL,
sale int
)
GO

INSERT INTO TEMPDB(sale) VALUES(80)
INSERT INTO TEMPDB(sale) VALUES(20)
INSERT INTO TEMPDB(sale) VALUES(120)
INSERT INTO TEMPDB(sale) VALUES(40)
go

select d, sale, (select sum(sale) from tempdb where d <= a.d) cumu_total
from tempdb a
GO

drop table tempdb
go

May the Almighty God bless us all!

monkeysee
Starting Member

1 Post

Posted - 2006-05-13 : 02:15:43

but where does the looping occur? Or doesonly 1 line gets displayed?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-15 : 04:29:33

quote:
Originally posted by monkeysee

but where does the looping occur? Or doesonly 1 line gets displayed?

The subquery will do it

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources