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 ...

Showing messages 1 through 3 of 3.

• accumulate a field
  2006-04-05 21:30:05  paul21_7 [Reply | 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 [Reply | 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 [Reply | 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