Calculating the sum of a column dynamically using lag function on the same un-computed column?-CodePudding

I'm trying to dynamically calculate a running total using the value in the past row:

The logic for computing dynamic_sum in the below table is : rooms lag(dynamic_sum)

    Day     rooms   dynamic_sum   logic
2021-01-04    1         1        (rooms lag(dynamic_sum) = 1 0)
2021-01-05    0         1        (rooms lag(dynamic_sum) = 0 1)
2021-01-06    2         3        (... = 2 1)
2021-01-07   -2         1
2021-01-08    2         3
2021-01-09   -2         1

Select Day, rooms, rooms lag(dynamic_sum) OVER() AS dynamic_sum from ....

I'm not able to implement this since, dynamic_sum column is getting used in real time.

ERROR:  column "dynamic_sum" does not exist
LINE 1: select Day, rooms, rooms lag(dynamic_sum...
                                     ^

Any help on how to achieve this kind of result would be greatly appreciated!

CodePudding user response：

You can use the sum window function:

select *,
sum(rooms) over(order by Day) as dynamic_sum
from table_name;

Fiddle