CODEWITHSUNDEEP
sqldb2ibm-midrange
Todd Hunter
Todd Hunter

Reputation: 881

Accumulate data over several days in sql query

I have two tables, the first (T1) is a base number, the first is a base value per symbol:

symbol       value
------------ -----
ABC           1000
DEF           2000

The second table (T2) s a series of values per date, i.e.:

date         symbol value
----------   ------ -----
2011-09-01   ABC      100
2011-09-02   ABC       10
2011-09-03   ABC        1

What query would I require to get the accumulated total of the value column added to the initial value in the first column over the several days. So the output of the query would look something like

symbol date       total
------ ---------- ------
ABC    2011-09-01   1100
ABC    2011-09-02   1110
ABC    2011-09-03   1111

So its the sum of the inital value in T1 plus the sum of all dates less than the date in that column.

The target database for this is DB2 on iSeries

Upvotes: 2

Views: 2053

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

An SQL approach

select 
   a.symbol, 
   a.value  --current value
     + COALESCE((select sum(value) 
        from T2 b 
        where 
           b.date < a.date and
           b.symbol=a.symbol
        ),0) --sum of history
     + c.value --initial value
from 
   t2 a join t1 c on (a.symbol = c.symbol)

Upvotes: 1

piotrp
piotrp

Reputation: 3884

You have to use window functions:

SELECT T1.symbol, T2.date, COALESCE(T1.value, 0)+sum(T2.value) over (PARTITION BY T2.symbol ORDER BY T2.date)
FROM T2
    LEFT JOIN T1 ON T1.symbol = T2.symbol
ORDER BY T1.symbol, T2.date

EDIT

Works on PostgreSQL, according to DB2 9.7 LUW docs it should work also on DB2.

Upvotes: 1

Related Questions