Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t (
  id number(6), 
  name varchar2(10), 
  amount number(6), 
  parentid number(6)
);
insert into t (id, name, amount, parentid) values (1, 'Adam', 1000, 0);
1 rows affected

insert into t (id, name, amount, parentid) values (2, 'John', 2000, 1);
1 rows affected

insert into t (id, name, amount, parentid) values (3, 'Clark', 1500, 2);
1 rows affected

insert into t (id, name, amount, parentid) values (4, 'Rita', 1200, 3);
1 rows affected

insert into t (id, name, amount, parentid) values (5, 'jack', 1600, 3);
1 rows affected

insert into t (id, name, amount, parentid) values (6, 'mark', 1800, 2);
1 rows affected

insert into t (id, name, amount, parentid) values (7, 'Finn', 1500, 6);
1 rows affected

insert into t (id, name, amount, parentid) values (8, 'Ryan', 1100, 6);
1 rows affected

select * from t order by id;


ID
NAME
AMOUNT
PARENTID


1
Adam
1000
0

2
John
2000
1

3
Clark
1500
2

4
Rita
1200
3

5
jack
1600
3

6
mark
1800
2

7
Finn
1500
6

8
Ryan
1100
6


with
n (root_id, current_id, name, amount, parentid) as (
  select id, id, name, amount, parentid from t
 union all
  select n.root_id, t.id, t.name, t.amount, t.parentid
  from n
  join t on t.parentid = n.current_id
)
select t.id, t.name, t.amount, t.parentid,
  sum(n.amount) - t.amount as downstreamsum 
from t
join n on n.root_id = t.id
group by t.id, t.name, t.amount, t.parentid
order by t.id


ID
NAME
AMOUNT
PARENTID
DOWNSTREAMSUM


1
Adam
1000
0
10700

2
John
2000
1
8700

3
Clark
1500
2
2800

4
Rita
1200
3
0

5
jack
1600
3
0

6
mark
1800
2
2600

7
Finn
1500
6
0

8
Ryan
1100
6
0

ID	NAME	AMOUNT	PARENTID
1	Adam	1000	0
2	John	2000	1
3	Clark	1500	2
4	Rita	1200	3
5	jack	1600	3
6	mark	1800	2
7	Finn	1500	6
8	Ryan	1100	6

ID	NAME	AMOUNT	PARENTID	DOWNSTREAMSUM
1	Adam	1000	0	10700
2	John	2000	1	8700
3	Clark	1500	2	2800
4	Rita	1200	3	0
5	jack	1600	3	0
6	mark	1800	2	2600
7	Finn	1500	6	0
8	Ryan	1100	6	0