CODEWITHSUNDEEP
sqloracle-database
Evo
Evo

Reputation: 61

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

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.

Upvotes: 3

Views: 479

Answers (4)

Colin MacMillan
Colin MacMillan

Reputation: 21

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

Upvotes: 0

Popeye
Popeye

Reputation: 35910

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

Upvotes: 0

hotfix
hotfix

Reputation: 3396

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

Upvotes: 0

MT0
MT0

Reputation: 168137

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

Upvotes: 3

Related Questions