Hierarchy Query with Rolling Total

Database
Enthusiast

Hierarchy Query with Rolling Total

Hi, In the  below hierarchical data, the top most managers don't have salary data, want to add-up lower most salary data of  the subordinates to the immediate higher level managers. This is just an example for my similar requirement, I'm able to get the hierarchy output, would like to have the rolling total as well, please help, thank you. EmpID Emp Name Mgr ID Level Calculation                         Output 28000 Mark         NULL 1          Steve=6200 + Anna=6000   12200 28001 Steve         28000 2          Lorena=5000 + Simon=1200    6200 28002 Anna         28000 2          Peter=2500 + Paul=6000   8500 28008 Paul                 28002 3         6000                                  6000 28004 Simon         28001 3         1200                                          1200 28007 Peter         28002 3         2500                                  2500 28003 Lorena         28001 3         Patrix=5000                          5000 28005 Patrix         28003 4         Zoya=5000                          5000 28006 Zoya         28005 5         5000                                  5000 Below are the scripts. CREATE MULTISET TABLE TMP_EMP_DATA ( EMP_ID INTEGER, MGR_ID INTEGER, EMP_NAME VARCHAR(10), SALARY decimal(38,2) ) PRIMARY INDEX (EMP_ID,MGR_ID); INSERT INTO TMP_EMP_DATA VALUES (28000,NULL,'Mark'); INSERT INTO TMP_EMP_DATA VALUES (28001,28000,'Steve'); INSERT INTO TMP_EMP_DATA VALUES (28002,28000,'Anna'); INSERT INTO TMP_EMP_DATA VALUES (28003,28001,'Lorena'); INSERT INTO TMP_EMP_DATA VALUES (28004,28001,'Simon',1200); INSERT INTO TMP_EMP_DATA VALUES (28005,28003,'Patrix'); INSERT INTO TMP_EMP_DATA VALUES (28006,28005,'Zoya',5000); INSERT INTO TMP_EMP_DATA VALUES (28007,28002,'Peter',2500); INSERT INTO TMP_EMP_DATA VALUES (28008,28002,'Paul',6000); Hierarchy Query WITH RECURSIVE REC_EMP_CHILD     (        EMP_ID     , EMP_NAME     , MGR_ID     , lvl      )       AS     (       SELECT         A.EMP_ID       , A.EMP_NAME       , A.MGR_ID       , 1       FROM         TMP_EMP_DATA A       WHERE A.EMP_NAME = 'Mark'       UNION ALL        SELECT         A.EMP_ID       , A.EMP_NAME       , A.MGR_ID       , B.lvl + 1       FROM         TMP_EMP_DATA A       JOIN         REC_EMP_CHILD B       ON B.EMP_ID = A.MGR_ID     )     SELECT *     FROM REC_EMP_CHILD