how to do a iterative rollup in sql

Question

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

Answer 1

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

In MySQL:

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,
        mytable
ORDER BY
        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)