How to extract the number of subordinates from manager_id in the same table?

user_6822323 0
Viewed 124

I've two columns: one with employee_id and one with manager_id Apart from the President with employee_id 100 who doesn't have a manager (so manager_id is null) the rest have managers. For example, the President is the manager for two people with manager_id of 100. How to count and place it in this way: employee_id 100 (column1) has 2 subordinates (column2)?

tried count, sum ,case, subquery and did't work

select employee_id,
       manager_id,
       first_name,
       last_name,
       case when employee_id = manager_id then count(employee_id) end,
       count(manager_id)
from   employees
--where manager_id is not null
group by manager_id,
       employee_id,
       first_name,
       last_name
--having sum(manager_id) > 5
order by employee_id;

I expect to have 1st column as employee_id and second as the counted subordinates per employee_id.

oracle sql
4 Answers

Use a correlated hierarchical query:

Oracle Setup:

CREATE TABLE employees ( employee_id, manager_id ) AS
  SELECT 100, NULL FROM DUAL UNION ALL
  SELECT 101,  100 FROM DUAL UNION ALL
  SELECT 102,  101 FROM DUAL UNION ALL
  SELECT 103,  102 FROM DUAL UNION ALL
  SELECT 104,  103 FROM DUAL UNION ALL
  SELECT 105,  101 FROM DUAL UNION ALL
  SELECT 106,  105 FROM DUAL UNION ALL
  SELECT 107,  106 FROM DUAL UNION ALL
  SELECT 108,  101 FROM DUAL UNION ALL
  SELECT 109,  108 FROM DUAL;

Query:

SELECT employee_id,
       (
         SELECT COUNT(*)
         FROM   employees s
         START WITH s.manager_id = e.employee_id
         CONNECT BY PRIOR employee_id = manager_id
       ) AS num_subordinates
FROM   employees e

Output:

EMPLOYEE_ID | NUM_SUBORDINATES
----------: | ---------------:
        100 |                9
        101 |                8
        102 |                2
        103 |                1
        104 |                0
        105 |                2
        106 |                1
        107 |                0
        108 |                1
        109 |                0

db<>fiddle here

user_15092640

if i understand your question, you could also do it with a simple Group by this will count only the subordinates not the whole hierarchy

with tab as(
  select 1 as emp_id, null as man_id from dual union all
  select 2 as emp_id, 1 as man_id from dual union all
  select 3 as emp_id, 1 as man_id from dual union all    
  select 2 as emp_id, null as man_id from dual union all
  select 5 as emp_id, 2 as man_id from dual 
)

select man_id as employee_id
      , count(1) as cnt
from tab
where man_id is not null
group by man_id

EMPLOYEE_ID | CNT
----------: | --:
          2 |   1
          1 |   2

db<>fiddle here

user_17152550

Try this:

-- data preparation
WITH EMPS AS 
(
SELECT 1001 AS EMP_ID, 'emp11' AS POS, 100 AS MGR_ID FROM DUAL UNION ALL
SELECT 1002 AS EMP_ID, 'emp12' AS POS, 100 AS MGR_ID  FROM DUAL UNION ALL
SELECT 1003 AS EMP_ID, 'emp13' AS POS, 100 AS MGR_ID  FROM DUAL UNION ALL
SELECT 2001 AS EMP_ID, 'emp21' AS POS, 200 AS MGR_ID  FROM DUAL UNION ALL
SELECT 2002 AS EMP_ID, 'emp22' AS POS, 200 AS MGR_ID  FROM DUAL UNION ALL
SELECT 100 AS EMP_ID, 'mgr1' AS POS, 1 AS MGR_ID  FROM DUAL UNION ALL
SELECT 200 AS EMP_ID, 'mgr2' AS POS, 1 AS MGR_ID  FROM DUAL UNION ALL
SELECT 1 AS EMP_ID, 'President' AS POS, NULL AS MGR_ID FROM DUAL )
-- Your actual query starts from here
SELECT
    EE.EMP_ID,
    EE.POS,
    EE.MGR_ID,
    CASE
        WHEN EC.CNT IS NULL THEN 0
        ELSE EC.CNT
    END AS CNT
FROM
    EMPS EE
    LEFT JOIN (
        SELECT
            MGR_ID,
            COUNT(1) AS CNT
        FROM
            EMPS
        GROUP BY
            MGR_ID
    ) EC ON EE.EMP_ID = EC.MGR_ID
ORDER BY
    EE.EMP_ID;

Please add the other condition according to your needs.

DB Fiddle demo

user_115656290

Try this, use a select within the select 

select emp.employee_id,
           emp.manager_id,
           emp.first_name,
           emp.last_name,
          (SELECT SUM(employees.employee_id) FROM employees where employees.manager_id=emp.employee_id) as subordinates,
           count(manager_id)
    from   employees emp
user_114766960
Related