Running total with condition and always looking at the previous value

I want to do a sequential sum in a table by taking into consideration a few extra conditions. We need to make sure that when the sum is taken sequentially so if a id has +40 then the next sum would be 130, if the next one is +1, the sum is still 130, now if the next one is -1 then the sum has to be 129. 100 needs to be added to the sum for the first time and from there on just the count should be added depending on condition. We need to even cap the min value of sum so it can’t be less than 70

I have tried the query below but it does not seem to look at the prior value.

Example that I tried:

create table tableA (id int not null, count int not null);  
insert into tableA(id, count) values(1,11), (2,21),(3, -3); -- case 1  
insert into tableA(id, count) values(1,35), (2,-3); -- case 2
insert into tableA(id, count) values(1,-45),(2,67); -- case3

Query tried:

 select t.id, t.count,
 case when (100+(select ifnull(sum(count),0) from tableA x where x.id <= t.id)) >= 130 then 130
      when (100+(select ifnull(sum(count),0) from tableA x where x.id <= t.id)) <= 70 then 70
      else (100+(select ifnull(sum(count),0) from tableA x where x.id <= t.id))     
 end  as xxxx
 from tableA t;

I expect my output to look like:

Case1 Result:

id  count   Sum
1   11      111
2   21      130
3   -4      126

Case2 Result:

id  count   Sum
1   35      130
2   -3      127

Case3 Result:

id  count   Sum
1   -45     70
2   67      137

Answer

THIS ANSWERS THE ORIGINAL VERSION OF THE QUESTION.

I think this does what you want:

select a.*, (@sum := least(@sum + count, 130)) as "sum"
from (select a.*
      from tablea a
      order by a.id
     ) a cross join
     (select @sum := 0) params;

I don’t understand where the 100 is coming from. It is not part of your explanation.

Here is a db<>fiddle that illustrates how this works using 30 as the limit (which seems to be your intention).

Advertisement

Answer