postgresql - Recursive count sql -

i have table this:

id    activity            pay       parent    1     pay             -         null      2     pay tax             10 $      1         3     pay water bills     -         1         4     fix house           -         null      5     fix roof            1 $       4           6     pay drinking water  1 $       3         

i want table this:

id    activity            pay       parent   matriks 1     pay             {11 $}    null     1       (pay tax + pay water bills) 2     pay tax             10 $      1        1-2 3     pay water bills     {1 $}     1        1-3     (pay drinking water) 4     fix house           {1 $}     null     4       (fix roof) 5     fix roof            1 $       4        4-5      6     pay drinking water  1 $       3        1-3-6 

count child parent: problem when water bills not counted drinking water, pay cant counted if pay tax or pay water not have pay value.

i tried on our postgres db (version 8.4.22), since fiddle bit slow taste. sql can pasted in there , works postgres.

still here fiddle demo take 20 sec first time faster.

here's produces calculated results me. (i didn't format according requirements, because in mind main excercise calculation.) assumes table called activity:

with recursive rekmatriks as(     select id, activity, pay, parent, id::text matriks, 0 lev         activity         parent null     union     select activity.id, activity.activity, activity.pay, activity.parent,            rekmatriks.matriks || '-' || activity.id::text matriks,            rekmatriks.lev+1 lev         activity inner join rekmatriks on activity.parent = rekmatriks.id ) , reksum (     select id, activity, pay, parent, matriks, lev, coalesce(pay,0) subsum         rekmatriks         not exists(select id rekmatriks rmi rmi.parent=rekmatriks.id)     union     select rekmatriks.*, reksum.subsum+coalesce(rekmatriks.pay, 0) subsum         rekmatriks inner join reksum on rekmatriks.id = reksum.parent)  select id, activity, pay, parent, matriks, sum(subsum) amount, lev     reksum group id, activity, pay, parent, matriks, lev order id 

as bonus, delivers nesting depth of id. 0 parent, 1 first sublevel etc. uses 2 recursive with queries achieve want. calculated value need in amount column.

the first 1 (rekmatriks) processes ids in table top bottom, starting ids have parent of null. recursive part takes parent id , adds it's own id it, achieve matriks tree representation field.

the second 1 (reksum) works bottom top , starts rows have no child elements. recursive part of query selects parent row each child row selected in non-recursive part, , computes sum of pay , subsum each line. produces multiple rows per id, since 1 parent can have multiple children.

all that's left final select statement. uses group by , sum aggregate multiple possible child sum values 1 row.

this work particular example. may fail if there's different cases not shown in sample data, example, if item has children carries value needs added.