# Miscellaneous > SQL Scripts >  Cumulative Total

## Daredevil

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 . :Frown:

----------


## JBane

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

Jeff

----------


## Daredevil

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

----------


## JBane

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

----------


## lbonnes

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

----------

