Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Location
    Sweden
    Posts
    3

    Question Unanswered: Accumulated rows in a query

    I am building a database for economicaly prediction in a company.
    I have a query which orders all post cronologicaly.
    Two of the fields are Amount and Accumulated. For each row the Amount should be added to the Accumulated on the previous row and saved to the Accumulated field on the current row.

    Example:
    Amount Accumulated
    ? 200
    50 250
    -100 150
    300 450
    and so on.

    I have tried to solve this but I cant find out how..
    Does anyone have any suggestions?

    (It should be a quick counted method sinse the query may contain around 20 000 rows)

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    You wil first need a primary key to find the previous row.
    Than you could use a trigger to update your second field. Inside your trigger you have to do the calculation.

    You could also create a function: get_previous_value() wich will return the value of the previous row.. (which is actually at that time the latest row)

    and than use:
    insert into table values (xxx, get_previous_value()+xxx);
    rws

  3. #3
    Join Date
    Aug 2002
    Location
    Sweden
    Posts
    3
    I know how to do the trigged function..
    What I odn't know is how to refer to the previous post in the ordered query. Each post has an unique ID but it isn't useful sinse the order of the IDs changes when you reorder the query.
    Is there any specific query id which is updated as the query is ordered.
    A new example
    qID, pID, Date, Amnt, Acc
    0, 3, 02-08-20, 50, 50
    1, 8, 02-08-20, -100, -50
    2, 1, 02-08-21, 200, 150
    3, 5, 02-08-23, 30, 180
    4, 12, 02-08-25, -30, 150
    5, 2, 02-08-28, 100, 250

    (Field description: queryID, postID, Date, Amount, Accumulated)

    It is as far as I know only the call to refer the previous post in the ordered query that I don't know how to write. Is there is any command to do this?

  4. #4
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    In my opinion the following qry gives you the correct result before you insert the new row.

    select acc
    from tab1
    where qid = MAX(qid)

    Why would you want to reorg the qry?
    rws

  5. #5
    Join Date
    Aug 2002
    Location
    Sweden
    Posts
    3
    maybe i expressed myself a little bad.. new posts are added all the time everywhere in the date order, so everytime i need to use the acc value it has to be recalculated

  6. #6
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    If your queryId is an automatically generated field

    SELECT Accumulated
    FROM YourTable
    WHERE max(QueryId) = QueriId

    returns the latest Accumulated value of the latest Query.

    Put that in a function. Return the value and run the function in your insert statement.
    rws

Posting Permissions

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