Thursday, August 4, 2011

Cumulative Sum in Postgresql

0 comments

I used Postgresql 9.0.1.


Created a test table:
CREATE TABLE cum_sum_test ( id int, amt int);

Populate data:
INSERT INTO cum_sum_test SELECT c,c*100 from GENERATE_SERIES(1,10) as c;

Cumulative Sum Query:
SELECT id , amt, sum(amt) over (order by id) FROM cum_sum_test;

Result:
 id | amt  | sum
----+------+------
  1 |  100 |  100
  2 |  200 |  300
  3 |  300 |  600
  4 |  400 | 1000
  5 |  500 | 1500
  6 |  600 | 2100
  7 |  700 | 2800
  8 |  800 | 3600
  9 |  900 | 4500
 10 | 1000 | 5500
(10 rows)

Continue reading →

Labels