By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename([id] int, [foreignkey] int, [value1] int, [value2] int);
;
INSERT INTO tablename([id], [foreignkey], [value1], [value2]) VALUES
('1', '500', '-100', '0'),
('2', '500', '900', '15'),
('3', '500', '570', '25'),
('4', '999', '100', '57'),
('5', '999', '150', '0');
5 rows affected
select id, foreignkey, value1 + coalesce(total, 0) value1, value2
from (
select *,
sum(case when value2 = 0 then value1 end) over (partition by foreignkey) total
from tablename
) t
where value2 <> 0
| id | foreignkey | value1 | value2 |
|---|---|---|---|
| 2 | 500 | 800 | 15 |
| 3 | 500 | 470 | 25 |
| 4 | 999 | 250 | 57 |
Warning: Null value is eliminated by an aggregate or other SET operation.