Results 1 to 5 of 5

Thread: Cumulative Total

  1. #1
    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. #2
    Join Date
    Dec 2002
    Posts
    181
    select doc_date, sum(amt) from bankaccount group by doc_date;

    Jeff

  3. #3
    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. #4
    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. #5
    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
  •