Oracle 18c

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

ID	RESULT	SOURCEID
a1	765	a5
a2	3298	a4
a3	8762	a8
a4	3298	null
a5	775	a6
a6	null	null
a7	10	a5