Oracle Analytic Functions - Getting the running sum for a column in a query

This is one of those things that I should have known - but didn't and had to figure out...

I needed to keep a running sum of a field in a query. I thought it had something to do with the "OVER ()" command but I finally found the answer:
"OVER (ORDER BY my_number ROWS UNBOUNDED PRECEDING)"

Below you'll find an example and Happy Holidays to all...Rich


SQL>CREATE TABLE test (
2 my_number NUMBER);

Table created.

SQL>INSERT INTO TEST VALUES (1);

1 row created.

SQL>INSERT INTO TEST VALUES (2);

1 row created.

SQL>INSERT INTO TEST VALUES (3);

1 row created.

SQL>INSERT INTO TEST VALUES (4);

1 row created.

SQL>COMMIT;

Commit complete.

SQL>SELECT my_number,
2 sum(my_number) OVER
3 (ORDER BY my_number ROWS UNBOUNDED PRECEDING)
4 AS "RUNNING SUM"
5 FROM test
6 ORDER BY my_number;

MY_NUMBER RUNNING SUM
-------------- --------------
1 1
2 3
3 6
4 10

Comments

Anonymous said…
This comment has been removed by a blog administrator.