Take the 2-minute tour ×

Hi I want to do a rolling sum (of valx) based on previous row value of rolling sum(valy) and the current row value of valx

valy=previousrow_valy + valx

animal valx valy 
cat    1    1 
cat    3    4 
cat    2    6 
dog    4    4 
dog    6    10 
dog    7    17 
dog    8    25
share|improve this question
Which RDBMS are you using? –  Lukasz Lysik Sep 18 '09 at 14:37
I am using oracle 10g –  Arun Sep 18 '09 at 14:44
add comment

1 Answer

You should define an order here.

Assuming you want to order by somecolumn.

In Oracle and PostgreSQL 8.4:

SELECT  animal, valx, SUM(valx) OVER (PARTITION BY animal ORDER BY somecolumn) AS valy
FROM    mytable


SELECT  animal, valx,
        @valy := CASE WHEN @animal = animal THEN @valy + valx ELSE valx END AS valy,
        @animal := animal
FROM    (
        SELECT  @valy = 0,
                @animal := NULL
        ) vars,
        animal, somecolumn

In SQL Server, it is one of the rare cases when creating a CURSOR is more efficient.

All databases (including SQL Server) support ANSI syntax:

SELECT  animal, valx,
        SELECT  SUM(valx)
        FROM    mytable mi
        WHERE   mi.animal = mo.animal
                AND mi.somecolumn <= mo.somecolumn
        ) AS valy
FROM    mytable mo

, however, this is not efficient if there are many records in mytable, execution time grows as O(n^2)

share|improve this answer
The last query for SQL Server returns exactly the same data as in the input table. –  Lukasz Lysik Sep 18 '09 at 14:48
@Quassnoi Please take a look at my answer when you have a moment to see if you endorse the alternate approach I have provided. I admire your work on stackoverflow. –  Brian Feb 11 '13 at 1:04
@Brian: how exactly is it alternate? –  Quassnoi Feb 11 '13 at 9:36
@Quassnoi it is using partition by and has no inline view. The inline view is just to provide sample data so the query is executable as is. –  Brian Feb 13 '13 at 16:02
@Brian: please take a closer look to the first query in the post. –  Quassnoi Feb 13 '13 at 16:27
show 1 more comment

Your Answer


