running total - help - 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

General SQL Server Forums

New to SQL Server Programming

running total - help

Author

Topic

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 12:23:26

There's this column in my query that contains numbers, see:

select
i.Amount as Balance

the numbers are right, which is good.

Is it possible, though, to add them? I know that sounds stupid (think sum function), but I mean add them in a way that every new row adds from the row before it, such as below:

Currently like this:

row 1 = 600
row 2 = 400
row 3 = -300
...

Need it to be like this:

row 1 = 600
row 2 = 400+(row1) = 1000
row 3 = -300+(row2) = 700
...

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-06-08 : 12:48:00

This is known as a running total, and should be done in your front end.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 15:16:02

quote:
Originally posted by DonAtWork

This is known as a running total, and should be done in your front end.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

But how would I do it for this syntax? I removed all the other fields that need to be shown and am only SELECTing the one that needs to be running total'd:

Select
i.Amount as 'Balance'

From
ARHeader h
join ARDetail d on h.Voucher = d.Voucher and h.CompCode = d.CompCode
join ARItem i on d.[LineNo] = i.[LineNo] and d.Voucher = i.Voucher

Where
i.CompCode='Will' and h.AID=475568982 and d.TransNo=0

I tried a bunch of subqueries but I can't seem to find the right syntax to get the right answer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources