CODEWITHSUNDEEP
sqlpostgresqlsumsql-order-bywindow-functions
3abirji
3abirji

Reputation: 17

Using CTE to calculate cumulative sum

I want to write a non-recursive common table expression (CTE) in postgres to calculate a cumulative sum, here's an example, input table:

----------------------
 1  |  A  |  0  | -1
 1  |  B  |  3  |  1
 2  |  A  |  1  |  0
 2  |  B  |  3  |  2

An output should look like this:

----------------------
 1  |  A  |  0  | -1
 1  |  B  |  3  |  1
 2  |  A  |  1  | -1
 2  |  B  |  6  |  3

As you can see the cumulative sum of columns 3 and 4 are calculated, this is easy to do using a recursive CTE, but how is it done with a non-recursive one?

Upvotes: 0

Views: 608

Answers (2)

GMB
GMB

Reputation: 222462

Use window functions. Assuming that your table has columns col1, col2, col3 and col4, that would be:

select
    t.*,
    sum(col3) over(partition by col2 order by col1) col3,
    sum(col4) over(partition by col2 order by col1) col4
from mytable t

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You would use a window function for a cumulative sum. I don't see what the sum is in your example, but the syntax is something like:

select t.*, sum(x) over (order by y) as cumulative_sum
from t;

For your example, this would seem to be:

select t.*,
       sum(col3) over (partition by col2 order by col1) as new_col3,
       sum(col4) over (partition by col2 order by col1) as new_col4
from t;

Upvotes: 1

Related Questions