Cumulative Total

I've got a table Bank Acct
having two columns
namely doc_date and amt
i would like to have the cumulative total for amt datewise
Presently working on oracle v.7.3
can any one of u help me solve this problem at the earliest .:(

select doc_date, sum(amt) from bankaccount group by doc_date;

Jeff

I don't want to group it on the basis of doc_date , let me ellaborate it,
if there are 2 rows in my table
for eg:
Doc date Amt
01-mar-2002 20000
03-mar-2002 50000
07-mar-2002 8000

I need a output displaying the running total of amt according to the doc date
as mentioned below

Output
======

01-mar-2002 20000
03-mar-2002 70000
07-mar-2002 78000

I hope this will help u understand the
problem

Expecting a solution at the earliest

You can do this if your table has a sequential primary key. Given this table bankaccount:

PK doc_date amt
-------------------------------
1 01/01/2000 100.0000
2 01/01/2000 200.0000
3 01/02/2000 200.0000

You can achieve a running total with the following:



SELECT doc_date,
(SELECT SUM(amt)
FROM bankaccount AS t2
WHERE t1.pk >= t2.pk) AS
running_total
FROM bankaccount AS t1


Giving the desired result:

doc_date running_total
---------- ---------------------
01/01/2000 100.0000
01/01/2000 300.0000
01/02/2000 500.0000

If no sequential primary key exists, you can still get an accurate running total provided the doc_date column is unique:



SELECT doc_date,
(SELECT SUM(amt)
FROM bankaccount AS t2
WHERE t1.doc_date >= t2.doc_date) AS
running_total
FROM bankaccount AS t1

However, if the doc_date column has duplicate values, the total will be off for those dates.


Jeff

why do you not have a trigger that will update another table or a new column in the bankaccount table with the cumulative data?


for a new running_total summary table the trigger would look something like this....
if insert ( bankaccount )
BEGIN

update running_total
set total = total + inserted.amt,
docdate = inserted.docdate
from bankaccount, inserted

END