Mysql sum balance recursively for all the hierarchy

Question

I have a user table like this

userId	username	balance	parentId	parentInfo
1	john	1000	0	0,
2	roy	2000	1	1,0,
3	James	2000	1	1,0,
4	Allen	5000	2	2,1,0,
5	Nell	4000	2	2,1,0,
6	Andy	1500	5	5,2,1,0,

I want to list all the users and sum of the balance for all their downline like this

userId	username	balance	parentId	downlineBalance
1	john	1000	0	14500
2	roy	2000	1	10500
3	James	2000	1	0
4	Allen	5000	2	0
5	Nell	4000	2	1500
6	Andy	1500	5	0

Answer 1

WITH RECURSIVE
cte AS ( SELECT *, parentId upperId
         FROM test
       UNION ALL
         SELECT cte.userId, cte.username, cte.balance, cte.parentId, test.parentId
         FROM test
         JOIN cte ON test.userId = cte.upperId
       )
SELECT test.*, COALESCE(SUM(cte.balance), 0) downlineBalance
FROM test
LEFT JOIN cte ON test.userId = cte.upperId
GROUP BY test.userId

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=506baa4e100d2eb76de4321343c9ba3d