1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sql hierarchy count at each top level record

Discussion in 'SQL PL/SQL' started by yasar2002, Mar 7, 2017.

  1. yasar2002

    yasar2002 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Dubai
    Suppose there are multiple managers, How can we count number of employees under each top most level manager in a single SQL? There might be something small but I am not sure what I missed.

    MGR Count
    100 5
    200 2

    LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL
    ------------------------- ----------- ---------- ----------
    King1 100 1
    Cambrault1 148 100 2
    Bates1 172 148 3
    Bloom1 169 148 3
    Fox1 170 148 3
    Yasar1 173 148 3

    King2 101 1
    Cambrault2 148 101 2
    Bates2 172 148 3



    Code (SQL):
    SELECT last_name, employee_id, manager_id, LEVEL
          FROM employees
          START WITH employee_id = 100
          CONNECT BY PRIOR employee_id = manager_id
          ORDER SIBLINGS BY last_name;

    SELECT last_name, employee_id, manager_id, LEVEL
          FROM employees
          START WITH employee_id = 101
          CONNECT BY PRIOR employee_id = manager_id
          ORDER SIBLINGS BY last_name;