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.
You can do:
select
t.*,
(select count(*) from t c where c.path like t.path || '/%') as c_count,
i.income + (
select coalesce(sum(i.income), 0) from t c join i on i.user_id = c.id
where c.path like t.path || '/%'
) as c_income
from t
left join (
select user_id, sum(income) as income from i group by user_id
) i on i.user_id = t.id
Result:
id name path c_count c_income
--- ------ ------- -------- --------
1 John /1 2 820
2 Mark /2 1 1020
3 Kevin /1/3 1 500
4 Sarah /1/3/4 0 null
5 Andy /2/5 0 680
See example at DB Fiddle.