Rolling sum

[swarddb]

What i need is as follows;

date count
01 1
02 2
03 7
04 10

what i need is
date count rolling sum
01 1 1
02 2 3
03 7 10
04 10 20

select date, count(count) from table group by date;

Where do i go from here.

thanks!

 

[gregsimpson]

I'm not sure what RDBMS you are using and I am aware this is the ANSI sql forum. However in DB2 you can use OLAP functions, but I don't know if these are ANSI compliant.

Anyway in DB2 the following should satisfy your query.

select date,
count,
sum(count) over (order by date) as rolling_sum
from table
order by date.


date count rolling_sum
01 1 1
02 2 3
03 7 10
04 10 20



Hope this helps, it works fine under DB2 as I said.

 

[crufty]

I believe the straight sql version can be done like so:

select date,
count,
(select count
from table t1
where t1.date < t0.date)
from table t0
order by date;

NOTE: I didn't test this thouroghly and it is pretty early in the morning so be careful ;^) If your RDBMS has a function that helps, by all means, use it.

 

[PruSQLer]

Crufty,

You were so close. Must be the early hour. I tweaked your query to the following and it worked.

select date,
count,
(select sum(count )
from table t1
where t1.date <= t0.date)
from table t0
order by date;

Good job

Greg,

I tried using your solution in DB2 and I got

SQL0109N The &quot;OVER&quot; clause is not allowed. SQLSTATE=42601

Any idea why?