Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53

    Unanswered: counting childs...

    I got stuck with this.

    need a qry to get the emp_id and the count of reports to him.

    data ll be like

    emp id mgr
    ===========
    1
    2 1
    3 1
    4 2
    5 2
    6 3

    the output to be like this

    emp id count
    ============
    1 5
    2 2
    3 1
    4 0
    5 0
    6 0

    ideas pls....
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Almost the same problem was discussed here.

  3. #3
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    Thanks for the link little foot

    but I think the sql listed there gives the count of only the immediate reports. here we need to get the count of all the immediate reports and their reports and so on...
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try this:
    Code:
    SQL> select e1.empno, e1.ename, (select count(*) from emp e2
      2                              connect by prior empno=mgr
      3                              start with e2.mgr = e1.empno) as empcount
      4  from emp e1;
    
         EMPNO ENAME        EMPCOUNT
    ---------- ---------- ----------
          7839 KING               13
          7698 BLAKE               5
          7782 CLARK               1
          7566 JONES               4
          7788 SCOTT               1
          7902 FORD                1
          7369 SMITH               0
          7499 ALLEN               0
          7521 WARD                0
          7654 MARTIN              0
          7844 TURNER              0
          7876 ADAMS               0
          7900 JAMES               0
          7934 MILLER              0

  5. #5
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    Thanks tony
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •