Summation of tree values

Input data:

SQL> drop table t;

Table dropped

SQL>
SQL> create table t as (select 1 id, 20 val from dual union all
2 select 2 id, 30 val from dual union all
3 select 3 id, 15 val from dual union all
4 select 4 id, 100 val from dual)
5 /

Table created

SQL> commit;

Commit complete

SQL> select * from t;

ID VAL
---------- ----------
1 20
2 30
3 15
4 100

SQL>

Problem:
You want to build a hierarchy on that data and get the sum of the values of all preceding ancestors including current value.

E.g. your hierarchy is:

SQL> select t1.*, level, sys_connect_by_path(id,'/') path
2 from t t1
3 connect by prior id = id - 1
4 start with id = 1
5 /

ID VAL LEVEL PATH
---------- ---------- ---------- --------------
1 20 1 /1
2 30 2 /1/2
3 15 3 /1/2/3
4 100 4 /1/2/3/4

SQL>

Solution here could probably be a scalar subquery, which will build tree in a backward direction starting from the current id and will compute sum of all the childs (ancestors in the outer query) values:

SQL> select t1.*,
2 level,
3 sys_connect_by_path(id,'/') path,
4 (select sum(val)
5 from t t2
6 connect by prior id = id + 1
7 start with t2.id = t1.id) summ
8 from t t1
9 connect by prior id = id - 1
10 start with id = 1
11 /

ID VAL LEVEL PATH SUMM
---------- ---------- ---------- ------------- ----------
1 20 1 /1 20
2 30 2 /1/2 50
3 15 3 /1/2/3 65
4 100 4 /1/2/3/4 165

SQL>

So while we have 'prior id = id - 1' rule in the outer query we put 'prior id = id + 1' in a scalar subquery to move backward.

But if the query was built in the following way:

SQL> select t1.*,
2 level,
3 sys_connect_by_path(id, '/') path
4 from t t1
5 connect by prior id < id
6 start with id = 1
7 /

ID VAL LEVEL PATH
---------- ---------- ---------- -----------------
1 20 1 /1
2 30 2 /1/2
3 15 3 /1/2/3
4 100 4 /1/2/3/4
4 100 3 /1/2/4
3 15 2 /1/3
4 100 3 /1/3/4
4 100 2 /1/4

8 rows selected

SQL>

What should we put in the connect by part of a scalar subquery?
If we reverse condition to 'prior id > id' in the subquery - we get wrong result. And it is not needed to be clarified why.

SQL> select t1.*,
2 level,
3 sys_connect_by_path(id,'/') path,
4 (select sum(val)
5 from t t2
6 connect by prior id > id
7 start with t2.id = t1.id) summ
8 from t t1
9 connect by prior id < id
10 start with id = 1
11 /

ID VAL LEVEL PATH SUMM
---------- ---------- ---------- ------------ ----------
1 20 1 /1 20
2 30 2 /1/2 50
3 15 3 /1/2/3 85
4 100 4 /1/2/3/4 255
4 100 3 /1/2/4 255
3 15 2 /1/3 85
4 100 3 /1/3/4 255
4 100 2 /1/4 255

8 rows selected

SQL>

We need to get a backward motion in a particular order. And what can show us the correct order - right, SYS_CONNECT_BY_PATH.

The solution could be:

SQL> select t1.*,
2 (select sum(val)
3 from t t2
4 connect by level <= t1.lvl
5 and id = regexp_substr(t1.path,'[^/]+',1,t1.lvl - level + 1)
6 start with t2.id = t1.id) summ
7 from (select t.*, level lvl, sys_connect_by_path(id, '/') path
8 from t
9 connect by prior id < id
10 start with id = 1) t1
11 /

ID VAL LVL PATH SUMM
---------- ---------- ---------- ------------ ----------
1 20 1 /1 20
2 30 2 /1/2 50
3 15 3 /1/2/3 65
4 100 4 /1/2/3/4 165
4 100 3 /1/2/4 150
3 15 2 /1/3 35
4 100 3 /1/3/4 135
4 100 2 /1/4 120

8 rows selected

SQL>

So at every level - we cut out and determine what the ID should be.

BTW Put these conditions exactly in that order:

...level <= t1.lvl and id = regexp_substr(t1.path,'[^/]+',1,t1.lvl - level + 1)...

if you put it

...id = regexp_substr(t1.path,'[^/]+',1,t1.lvl - level + 1)and level <= t1.lvl...

you'll get:

ORA-01428: argument '0' is out of range

PS Later I wrote another note on the same kinda a problem, which you can discover here: Reports: getting total of all children values in a tree