Dynamically add previous row value to current row value in SQL(Postgres)

Question

I have a table with the following structure:

I would like to get the following result:

where col3= col3 value of previous row + col1 value - col2 value

I added a column of zeroes like this:

and tried the following query:

select col1, col2, 
lag(col3) over (order by id) + col1 - col2 as col3
from t1;

However I am not able to obtain the desired result. I would like to get some help with this.

SQL tables represent unordered sets. Without a column that specifies the ordering, there is no "previous" row or value. — Gordon Linoff
– Gordon Linoff, Commented Dec 31, 2020 at 15:25

Popeye · Accepted Answer · 2020-12-31 16:04:49Z

1

You just need analytical function sum as follows:

select col1, col2, 
       Sum(col1 - col2) over (order by id) as col3
  from t1;

answered Dec 31, 2020 at 16:04

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1