Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

SQL Query

sajjukazisajjukazi Member Posts: 1
Sample Data:

ENO MGR SAL
---------- ---------- ----------
1 10
10 1 20
101 10 30
102 10 40
15 1 20
2 40
20 2 30
25 2 20
201 20 30
205 20 40
251 25 30

Pls notice that at the highest level are eno 1 and 2.
These have siblings that inturn have siblings.

I want the sum of all siblings for the topmost level.

Result should be:

ENO SAL
---------- ----------
1 120
2 190

I need this ASAP.
pl help.






Comments

  • JohnMeliJohnMeli Member Posts: 1
    :If the table in which the data are is Emp_Sal, the sql statement
    :that will give you the result you are looking for is the following
    :
    :select a.eno eno, sum(b.sal) sal
    :from emp_sal a, (select eno, sal from emp_sal
    : start with mgr is null
    : connect by prior eno = mgr) b
    :where a.eno in (select eno from emp_sal where mgr is null)
    :and b.eno in (select eno from emp_sal
    : start with eno = a.eno
    : connect by prior eno = mgr)
    :group by a.eno
    :
    : ENO SAL
    :---------- ----------
    : 1 120
    : 2 190
Sign In or Register to comment.