SQL help please .. adding a running total column to a table
Ramky99Nov 9 2009 — edited Nov 27 2009Evening all.... I have the task of adding a running total column to a table that contains a list of transactions. The table contains entries for multiple accounts.
e.g.
create table adam_test1
(
account char(4),
entry_date date,
amount number(4),
balance number(4)
);
insert into adam_test1 values ('1000','01-jan-2000','50',null);
insert into adam_test1 values ('1000','02-jan-2000','40',null);
insert into adam_test1 values ('1000','03-jan-2000','-50',null);
insert into adam_test1 values ('1000','04-jan-2000','10',null);
insert into adam_test1 values ('1000','05-jan-2000','-250',null);
insert into adam_test1 values ('1000','06-jan-2000','100',null);
insert into adam_test1 values ('1000','07-jan-2000','50',null);
insert into adam_test1 values ('2000','01-jan-2000','30',null);
insert into adam_test1 values ('2000','02-jan-2000','10',null);
insert into adam_test1 values ('2000','03-jan-2000','-520',null);
insert into adam_test1 values ('2000','04-jan-2000','140',null);
insert into adam_test1 values ('2000','05-jan-2000','-4',null);
insert into adam_test1 values ('2000','06-jan-2000','120',null);
insert into adam_test1 values ('2000','07-jan-2000','57',null);
SQL> select * from adam_test1;
ACCO ENTRY_DAT AMOUNT BALANCE
---- --------- ---------- ----------
1000 01-JAN-00 50
1000 02-JAN-00 40
1000 03-JAN-00 -50
1000 04-JAN-00 10
1000 05-JAN-00 -250
1000 06-JAN-00 100
1000 07-JAN-00 50
2000 01-JAN-00 30
2000 02-JAN-00 10
2000 03-JAN-00 -520
2000 04-JAN-00 140
2000 05-JAN-00 -4
2000 06-JAN-00 120
2000 07-JAN-00 57
..I've tried a few things, using rowid, to populate the values but I've not been able to get anything to work - any ideas?
Thanks!
Adam