# Sql addition query

• If the sum of a group comes as negative. The negative numbers are to be increased (biggest negative first) to bring the total sum as 0

Example

Group 1 has this 4 values -4,-3,1,1

The total is negative

Thus it gets tweaked to

0,-2,1,1 .

-4 becomes 0 as it is the highest negative..

the balance is removed from -3 to make it to -2..

Thus the total is 0.

• I've added a few extra values to make sure I'm covering some edge cases.

The idea here is to look at the rolling sum, and figure out the point where the sum of the negative values goes past the overall sum. Before that point, the newvals are 0, after that point, they're the original value, and at that point, it's the difference between the rolling sum and the overall sum.

``````with vals as (select * from (values (1), (-3), (1), (-4), (-5), (-6), (2)) v(val))
,
ordered as
(
select v.val
, ROW_NUMBER() over (order by v.val) as rownum
, sum(v.val) over () as OverallTotal
from vals v
)
,
rollingsum as
(
select o.*
, sum(case when o.val < 0 then o.val else 0 end) over (order by o.rownum rows between unbounded preceding and current row) as RollingSumNegsByRownum
, sum(case when o.val < 0 then o.val else 0 end) over (order by o.rownum rows between unbounded preceding and 1 preceding) as RollingSumNegsByRownumPrev
from ordered o
)
select r.val
, case
when r.OverallTotal < 0 and r.val < 0 and r.OverallTotal  r.RollingSumNegsByRownum then r.RollingSumNegsByRownum - r.OverallTotal
else r.val
end as NewVal
from rollingsum r
;
``````

Results:

``````-6  0
-5  0
-4  -1
-3  -3
1   1
1   1
2   2
``````

And results on the original list:

``````-4  0
-3  -2
1   1
1   1
``````

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2