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 .:(
Printable View
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