Running Total/Cumulative Sum SQL That Doesn't Go Below 0

user_19138643 0
Viewed 27

I'm trying to create a cumulative sum column in SQL that resets to 0 once the cumulative sum goes below 0. Input:

ID  Amount
1   140
2   -50
3   -50
4   -50
5   10
6   -10
7   -5
8   20

Expected Output:

ID  Amount   Cum. Sum
1   140        140
2   -50        90
3   -50        40
4   -50        0
5   10         10
6   -10        0
7   -5         0
8   20         20

I've tried a conditional that makes the value 0 IF the cumulative sum is less than 0, but that doesn't work. The result I get gives ID 5 the cumulative sum of 0 instead of 10

postgresql sql
1 Answers

You can use a recursive CTE to achieve the result you want.

For example:

with recursive
n as (
  (select *, amount as cum_sum from t order by id limit 1)
 union all
  select t.*, greatest(t.amount + n.cum_sum, 0)
  from n
  join t on t.id = n.id + 1
)
select * from n order by id

Result:

 id  amount  cum_sum 
 --- ------- ------- 
 1   140     140     
 2   -50     90      
 3   -50     40      
 4   -50     0       
 5   10      10      
 6   -10     0       
 7   -5      0       
 8   20      20

See running example at DB Fiddle.

user_64361910
Related