Article:
  SQL Subqueries
Subject:   accumulate a field
Date:   2006-03-02 01:53:41
From:   st.chen
thanks in advance,


if i have a table having the following 2 fields:
date qty
-------- ---
20060101 100
20060102 120
20060103 111
...


can we get the following data using a SELECT statement?
date qty tqty
-------- --- ----
20060101 100 100
20060102 120 220
20060103 111 331
...


Full Threads Oldest First

Showing messages 1 through 3 of 3.

  • accumulate a field
    2006-04-05 21:30:05  paul21_7 [View]

    SELECT date,
    qty,
    (
    (SELECT qty
    FROM table b
    WHERE b.date = (a.date - 1))
    + qty
    ) tqty
    FROM table a

    Syntactically its fine ... I made a small assumption on date being the incremental field, so just alter to suit

    enjoy
    • accumulate a field
      2006-07-31 07:44:28  kaczmar [View]

      it doesn't really work for me. It accumulates a record above only... Is there actually a way to accumulate values in sql without using procedures?
      • accumulate a field
        2007-11-15 23:14:04  AdamBerko [View]

        Here is how I did it. Seems to work fine
        select ObjectID,StartDate,cscfActiveUsers,
        (
        select sum(cscfActiveUsers)
        from cscf_data b
        where b.StartDate<=a.StartDate
        and a.ObjectID=b.ObjectID
        ) as summ
        from cscf_data a
        order by ObjectID