I have a table like this:

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

I would like to have this:

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

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.

CodePudding user response：

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.