3

I've looked at several different answers on this site and others, but I'm not having much luck figuring out how to count siblings in a hierarchical query.

I'm using Oracle 10g.

SELECT LEVEL, last_name||', '||first_name AS Manager, count(employee_id)
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
GROUP BY level

This returns 4 levels, and about 80 employees. I'm wanting to add up the number of siblings under the level 2 instead of listing them all. But, I'm getting stuck trying to figure it out.

Any nudges towards the right way? Thanks!

Jake_TheCoder
  • 57
  • 1
  • 9

1 Answers1

2

This will count the number of descendents of each employee at level 1 and 2:

SELECT employee_id, manager_id, full_name, emp_level
    ,(
        SELECT COUNT(*)
        FROM employees
        START WITH employees.manager_id = employees2.employee_id
        CONNECT BY prior employee_id = manager_id
    ) descendents
FROM
(
    SELECT employee_id, manager_id, last_name||', '||first_name full_name, LEVEL emp_level
    FROM employees
    START WITH manager_id IS NULL
    CONNECT BY PRIOR employee_id = manager_id
) employees2
WHERE emp_level <= 2;
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • excellent! Thank you so much. Looks like my logic was goofy on this query. I tried counting them, but I did not lead off the query with the count, and do an inline view like you suggested. It makes sense to me now, creating a temp table to be able to do the START WITH employees.manager_id = employees2.employee_id. Thanks again!! – Jake_TheCoder Sep 26 '12 at 13:37