Hi Team,
We want to build a hierachy process bar with one SQL but failed. We decide to turn to you guys for help.
Plz allow me to put it in this way with a simple test.

>>> Here's my test data:

create table DEMP
(
  ename     VARCHAR2(128), --name
  eid       INTEGER,       -- id
  eparent   INTEGER,       -- parent_id
  eprogress INTEGER        -- progress. If some certain ID has sons, we DO NOT care what this progress value is, this value should be equal to avg of his sons. Else we would take the value as the value of progress. 
)
;



insert into DEMP (ename, eid, eparent, eprogress)
values ('ename1', 1, null, 11);
insert into DEMP (ename, eid, eparent, eprogress)
values ('ename2', 2, 1, 40);
insert into DEMP (ename, eid, eparent, eprogress)
values ('ename3', 3, 1, 60);
insert into DEMP (ename, eid, eparent, eprogress)
values ('ename4', 4, 1, 44);
insert into DEMP (ename, eid, eparent, eprogress)
values ('ename5', 5, 4, 20);
insert into DEMP (ename, eid, eparent, eprogress)
values ('ename6', 6, 4, 80);
insert into DEMP (ename, eid, eparent, eprogress)
values ('ename10', 10, null, 10);
insert into DEMP (ename, eid, eparent, eprogress)
values ('ename11', 11, 10, 100);
insert into DEMP (ename, eid, eparent, eprogress)
values ('ename12', 12, 10, 100);
insert into DEMP (ename, eid, eparent, eprogress)
values ('ename13', 13, 11, 13);
insert into DEMP (ename, eid, eparent, eprogress)
values ('ename14', 14, 13, 1);
insert into DEMP (ename, eid, eparent, eprogress)
values ('ename15', 15, 13, 20);
commit;

>>> our hierachy relationship looks like this:

SQL> select lpad(' ',level*2,' ')||SYS_CONNECT_BY_PATH(ENAME,'-> ') ENAME, eprogress from demp E start with EPARENT is null connect by prior E.EID=E.EPARENT;

ENAME                                                         EPROGRESS
------------------------------------------------------------ ----------
  -> ename1                                                          11 -- value of record is 11, but it should be the average of his sons(40, 60 and 50(average(20 and 80)))
    -> ename1-> ename2                                               40
    -> ename1-> ename3                                               60
    -> ename1-> ename4                                               44  --value of record is 44, but it should be the average of his sons(20 and 80)
      -> ename1-> ename4-> ename5                                    20
      -> ename1-> ename4-> ename6                                    80
  -> ename10                                                         10  --should be the caculated average value of (ename11 and ename12)
    -> ename10-> ename11                                            100  --value of record is 100, but it should be the average of his sons (ename13 only. so is 10.5 means average of (1+20))
      -> ename10-> ename11-> ename13                                 13  --value of record is 13, but it should be the average of his sons(1 and 20)
        -> ename10-> ename11-> ename13-> ename14                      1
        -> ename10-> ename11-> ename13-> ename15                     20
    -> ename10-> ename12                                            100  --100

I mean our goal should be shown like this:

SQL>   with tt as
  2   (select t.ename,
  3           t.eid,
  4           (case
  5             when ename in
  6                  (SELECT t.ename /* t.*,LEVEL*/
  7                     FROM demp t
  8                    WHERE EXISTS
  9                    (SELECT 1 FROM demp t2 WHERE t2.eparent = t.eid)
 10                    START WITH eparent is null
 11                   CONNECT BY PRIOR eid = eparent) then
 12              null           -- If there's any son, then assume the value to null
 13             else
 14              eprogress      -- If there's no son
 15           end) eprogress,
 16           t.eparent,
 17           SYS_CONNECT_BY_PATH(EID, '/') || '/' path
 18      from DEMP t
 19     start with t.eparent is null
 20    connect by prior t.eid = t.eparent)
 21  SELECT eid,
 22         eparent,
 23         eprogress,
 24         ename,
 25         (SELECT avg(eprogress) FROM tt WHERE path like t1.path || '%') s_value
 26    FROM tt T1 order by eid;

       EID    EPARENT  EPROGRESS ENAME                                                           S_VALUE
---------- ---------- ---------- ------------------------------------------------------------ ----------
         1                       ename1                                                               50
         2          1         40 ename2                                                               40
         3          1         60 ename3                                                               60
         4          1            ename4                                                               50
         5          4         20 ename5                                                               20
         6          4         80 ename6                                                               80
        10                       ename10                                                      40.3333333  -- WRONG! his son is ename11 and ename12, it should be (10.5+100)/2=55.25
        11         10            ename11                                                            10.5
        12         10        100 ename12                                                             100
        13         11            ename13                                                            10.5
        14         13          1 ename14                                                               1
        15         13         20 ename15                                                              20

I do not know what is wrong. the value of ename10 should be the average of his sons(10.5 and 100) rather than grandsons.
What will you do to relize this kind of requests?