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
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