By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test(id varchar(10), col1 int, sourceid varchar(20))
insert into test values('a1', 765, 'a5');
1 rows affected
insert into test values('a2', 3298, 'a4');
1 rows affected
insert into test values('a3', 8762, 'a8');
1 rows affected
insert into test values('a4', 3298, null);
1 rows affected
insert into test values('a5', null, 'a6');
1 rows affected
insert into test values('a6', null, null);
1 rows affected
insert into test values('a7', 10, 'a5');
1 rows affected
select id
, case when col1 is null then
(select sum(col1) from test tt where tt.sourceid = t.id)
else
col1
end result
, sourceid
from test t
order by id;
| ID | RESULT | SOURCEID |
|---|---|---|
| a1 | 765 | a5 |
| a2 | 3298 | a4 |
| a3 | 8762 | a8 |
| a4 | 3298 | null |
| a5 | 775 | a6 |
| a6 | null | null |
| a7 | 10 | a5 |
with cte as (select t.id
, case when t.col1 is null then
(select sum(tt.col1) from test tt where tt.sourceid = t.id)
else
t.col1
end result
, t.sourceid
from test t
order by id)
select t.id
,
case when t.result is null then
(select sum(tt.result) from cte tt where tt.sourceid = t.id)
else
t.result
end result
, t.sourceid
from cte t
order by id
| ID | RESULT | SOURCEID |
|---|---|---|
| a1 | 765 | a5 |
| a2 | 3298 | a4 |
| a3 | 8762 | a8 |
| a4 | 3298 | null |
| a5 | 775 | a6 |
| a6 | 775 | null |
| a7 | 10 | a5 |