Question 390280 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to COUNT the depth of hierarchy?

I have a table employees as below:

id name managerId
1 Annie Null
2 Bob Null
3 Chloe 1
4 Danny 2
5 Eddie 3
6 Felicia 3
7 Georgia 4
8 Harper 3

Top-level management is Annie and Bob as they don’t have managers above them (managerId is Null). I need to count the number of employees top-level managers are in charge. The expected output:

id name number of employees
1 Annie 4
2 Bob 2

Annie is the direct manager of Chloe, and Chloe is the direct manager of Eddie, Felicia and Harper, so the number returned is 4.

As far as I read, it seems I could try recursive query but I still don’t know how to do. Or please tell me if there are better options. Thank you.

Tags:

2

Answers


  1. 0

    You could try something like this:

    I hope the syntax is correct and works…

    with cte_managers as
(
  Select managerId,
         count(*) as NoOfEmployees
  From   employees
  Where managerId is not null
  Group By managerId 
)
Select e.id,
       e.name, 
       a.NoOfEmployees
from employees e 
inner join cte_managers as a on a.managerId= e.id
where e.managerId is null
order by e.name
    Login or Signup to reply.
  2. 0

    Here how to do it using the recursive CTE :

    WITH RECURSIVE employees_count (id, name, parent_id) AS (
  SELECT id, name, id as parent_id
  FROM employees 
  WHERE managerId IS NULL
  UNION ALL
  SELECT e.id, e.name, ec.parent_id
  FROM employees_count AS ec 
  JOIN employees AS e ON ec.id = e.managerId
)
SELECT parent_id as ID, COUNT(*) AS nb_employees
FROM employees_count
WHERE id <> parent_id
GROUP BY parent_id

    The nonrecursive SELECT produces the row for the top level managers (the row with a NULL managerId).

    Each row produced by the recursive SELECT finds all employees who report directly to an employee produced by a previous row

    Recursion ends when employees have no others who report to them.

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search