sql - sum of ltree children per level -

i have properties table ltree path , name columns. ltree path contains ids of parent nodes, i.e. "7968.7969.7987.8000". each tree node have reports numeric value. need find sum of children nodes values each node in ltree. path in round brackets

        (1)            |      __________________________________      |                |                | (1.2)b            (1.3)c            (1.4)d | |________________________         |                | (1.2.5) e         (1.2.6) f 

reports

property_id | value     1       |   1      2       |   4     3       |   19     4       |   21     5       |   9     6       |   11 

i need find smth like

full_path | subtree_sum         |     60     (1 + 4 + 19 + 21 + 9 + 11)    a.b    |     24     (4 + 9 + 11)      a.c    |     19     (19)    a.d    |     21     (21)    a.b.e  |     9      (9)    a.b.f  |     11     (11) 

here are:

select      p.path,     sum(r.value) properties p left join properties sub on sub.path::text p.path::text||'%' left join reports r on r.property_id=sub.id group 1;  

how work?

for each node called in our query p retrieving subnodes (including itself) joining sub. join we're using like operator allows use p path prefix. quick examples should give understanding of like operator (% wildcard):

select 'prefix1' 'prefix1%'; --true select 'prefix1.something' 'prefix1%'; --true select 'prefix2' 'prefix1%'; --false select 'prefix2.something' 'prefix1%'; --false 

last step join each subnode value, sum , group first column.

edit:

i've educated myself bit , found better solution:

select      p.path,     sum(r.value) properties p left join properties sub on sub.path <@ p.path left join reports r on r.property_id=sub.id group 1;  

it's better since <@ operator utilizes existing gist indexes.