Results 1 to 5 of 5

Thread: Sum Aggregation

  1. #1
    Join Date
    Feb 2006
    Posts
    2

    Unhappy Unanswered: Sum Aggregation

    I have a problem such that

    for example I have a table that contains data like below

    name --- total --- remainder
    ayhan --- 100 --- 100
    ayhan --- 200 --- 200
    ayhan --- 50 --- 50

    but I want to display it such like that

    name --- total --- remainder
    ayhan --- 100 --- 100
    ayhan --- 200 --- 300
    ayhan --- 50 --- 350

    so I want the sum of each row of remainder with previous one
    what is the sql statement for this?

    Thanks...
    I am using delphi (ado and ms access)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    without a column which dictates the sequence of the rows, you cannot do it

    "previous" has meaning only in a flat file

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Posts
    2

    in oracle they can do that

    SELECT NAME ,
    total,
    SUM(REMAINDER) OVER (ORDER BY ROWID) running_sum
    FROM TEST;

    OUTPUT
    ________

    NAME TOTAL RUNNING_SUM
    ayhan 100 100
    ayhan 200 300
    ayhan 50 350

    why can we not do with ms access or is possible

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why? because
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You could achieve this with a sub select but, as Rudy points out, you would need to impose an order to your rows.

    More easily achieved in a report though - check out the running sum property of text boxes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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