'Count children from hierarchy path

I have a table like this:

id name path
1 John /1
2 Mark /2
3 Kevin /1/3
4 Sarah /1/3/4
5 Andy /2/5
... ... ...

So, I can say that Sarah is Kevin's child which is John's child.

I would like to have this:

id name path number of children
1 John /1 2
2 Mark /2 1
3 Kevin /1/3 1
4 Sarah /1/3/4 0
5 Andy /2/5 0
... ... ... ...

TASK NUMBER 2: Let's say that I have this table too

id income user_id
1 200 1
2 120 1
3 340 2
4 500 3
5 600 5
6 80 5

I can say that John has a Total income of 320$, but if I also want to count John's children, it is 820$ (because id =3 is John's child). So, I would also like a query where I can count all the hierarchical incomes.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source