MySQL 8.0

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

create table yourtable(id int, `Referral id` int, Balance int);
insert into yourtable values (1, 0, 1000), (2, 1, 1200), (3, 0, 1300), (4, 2, 1000),
(5, 4, 1100), (6, 0, 1700), (7, 6, 2200), (8, 6, 2000), (5, 4, 1100), (9, 8, 1000),
(10, 9, 1200);
select * from yourtable;


id
Referral id
Balance


1
0
1000

2
1
1200

3
0
1300

4
2
1000

5
4
1100

6
0
1700

7
6
2200

8
6
2000

5
4
1100

9
8
1000

10
9
1200


with recursive u as
(select t.id, t.`Referral id`, t.Balance from yourtable t where id = 1
union
select t.id, t.`Referral id`, t.Balance from u inner join yourtable t
on u.id = t.`Referral id`)
(select sum(Balance) from u)


sum(Balance)


4300

id	Referral id	Balance
1	0	1000
2	1	1200
3	0	1300
4	2	1000
5	4	1100
6	0	1700
7	6	2200
8	6	2000
5	4	1100
9	8	1000
10	9	1200