Recursive query help

Hi faculties,

    I have this query and the output it gives is as follows:

SQL> select rpad('*',2*level,'*') || ename name, sal,

               (select sum(e2.sal)
                  from emp e2
                     start with e2.empno = e1.empno
               connect by prior e2.mgr = e2.empno) tot_sal
      from emp e1
     start with e1.mgr is null
     connect by prior empno = mgr;

NAME                                  SAL    TOT_SAL
------------------------------ ---------- ----------


**KING                               5000       5000

****JONES                            2975       7975

******SCOTT                          3000      10975

********ADAMS                        1100      12075

******FORD                           3000      10975

********SMITH                         800      11775

****BLAKE                            2850       7850

******ALLEN                          1600       9450

******WARD                           1250       9100

******MARTIN                         1250       9100

******TURNER                         1500       9350

******JAMES                           950       8800

****CLARK                            2450       7450

******MILLER                         1300       8750

Here the total sum is displayed at the child level. Instead i want the sum of the parents and childs to be displayed at the root level and sublevels only. So the leaf level will show only its salary.The immediate parent should show the sum of its salary and the child's salary and so on till the root level. Can the existing be modified to achieve the desired results?

Thanks in advance
Jackal Received on Tue Dec 26 2006 - 06:20:01 CST