accounts + balance

[vlitim]

I have a table which has trasactions in it which have an amount.

ie

id amount
1 50000
2 400
3 -378
4 344

I need to add a new column showing what the balance was at every transaction point but am stuck on how to do it in an efficient way, can anyone help please?

Cheers
tim

 

[vongrunt]

This is called "running total":

select id, amount, 
	(select sum(amount) from myTable B where id< A.id) as balance
from myTable A
order by A.id

If you change < to <=, balance column will contain value after transaction.

Similar examples:

http://databasejournal.com/features/mssql/article.php/3112381

Other methods:

http://www.sqlteam.com/item.asp?ItemID=3856

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba