1. Registered User
Join Date
Jan 2003
Location
Australia
Posts
2

## 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 .

2. Registered User
Join Date
Dec 2002
Posts
181
select doc_date, sum(amt) from bankaccount group by doc_date;

Jeff

3. Registered User
Join Date
Jan 2003
Location
Australia
Posts
2
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

4. Registered User
Join Date
Dec 2002
Posts
181
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
Last edited by JBane; 01-29-2003 at 03:21 PM.

5. Registered User
Join Date
Jan 2003
Location
usa
Posts
1
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•