SQL Query to Get Recursive Count of Employees Under Each Manager

nested query to get number of employees under managers

Here you go!!

With recursive cte as (
     select id,Managerid from employee  --> Anchor Query
     union all
     select c.Id,e.ManagerId from cte c --> Recursive Member
     join employee e on (c.ManagerId=e.Id)) --> Termination Condition
select ManagerId,count(Id) as Number_of_Employees
from cte group by ManagerId

Demo

Update

Ok let me try to explain.

First we need to generate a table that lists employees under manager and that manager's manager till top level(all combinations). It should be something like below right? Lets call this as Resultant Table

-------------------------
|   Id  |   ManagerId   |
-------------------------
|   1   |       3       |--
|   2   |       3       |  |
|   3   |       6       |  |
|   4   |       7       |  |->From your table
|   5   |       7       |  |
|   6   |       8       |  |
|   7   |       6       |-- 
|   2   |       6       |--
|   1   |       6       |  |
|   7   |       8       |  |
|   3   |       8       |  |
|   5   |       6       |  |-> Nested structure which you meant in the question
|   4   |       6       |  |
|   4   |       8       |  |
|   5   |       8       |  |
|   1   |       8       |  |
|   2   |       8       |--
-------------------------

Once we achieve the above table, it is straight forward query to get the count using group by ManagerID. So how are we going to achieve this.

1) We can get the direct employees using

select Id,ManagerId from employee -- let's call this as QueryE1

2) Now lets join with the same table to get first level managers with their employees

select e1.Id,e2.ManagerId from employee e1 join employee e2 on e1.managerid = e2.id     
--QueryE2
-------------------------
|   Id  |   ManagerId   |
-------------------------
|   1   |       6       |
|   2   |       6       |
|   3   |       8       |
|   7   |       8       |
|   4   |       6       |
|   5   |       6       |
-------------------------

3) Then we should consider the above table as Reference table(QueryE2) and find out second level managers with their employees by joining with employee table. Here since 8 is manager of 6 all reportees of 6 is also reportees of 8.

SELECT e3.id,e4.managerid 
FROM   (SELECT e1.id,e2.managerid 
        FROM employee e1 JOIN employee e2 
                 ON e1.managerid = e2.id) e3 
JOIN employee e4 
ON e3.managerid = e4.id -- QueryE3

-------------------------
|   Id  |   ManagerId   |
-------------------------
|   1   |       8       |
|   2   |       8       |
|   4   |       8       |
|   5   |       8       |
-------------------------

4) We should repeat the above steps until there are no more Managers for Managers. Now we know there is no manager for 8. But lets see what query says. Now we should consider the latest table(above) as the reference table.

SELECT e5.id,e6.managerid 
FROM   (SELECT e3.id,e4.managerid 
        FROM   (SELECT e1.id,e2.managerid 
                FROM   employee e1 
                       JOIN employee e2 
                         ON e1.managerid = e2.id) e3 
        JOIN employee e4 
        ON e3.managerid = e4.id) e5 
JOIN employee e6 
ON e5.managerid = e6.id  --QueryE4-- returns 0 rows as well

Finally if we combine(union) all the values from all these queries we will get the required Resultant Table. This entire thing is done by our RECURSIVE CTE in a single query. Here QueryE1 is the Anchor Query. QueryE2,QueryE3 & QueryE4 are the Recursive Members and these are created by our CTE until we get 0 rows. Once the Resultant Table is created, we can use that to customize our needs. Here we are doing Group by to get the count of ManagerID. I'm not sure whether it will clear your confusions but at least hope you will get an idea :)

Get employee count under managers recursively in MySQL

select max(cnt)
  from (
   select @ids:=(
             select group_concat(id)
               from empTab
              where find_in_set(managerID,@ids)>0
          ),
          @cnt:=@cnt+(select count(1)
                        from empTab
                       where find_in_set(id,@ids)>0) as cnt
     from empTab E,
          (select @cnt:=0,@ids:=3) X  -- <--- @ids is starting ID for search 
    limit 6  -- <-- set limit to maximal Tree nesting level or more
  ) X

Is slow, but works ...

SQL Query to get name of all employees under a manager recursively

You need to use CONNECT BY to process hierarchical data in the table:

https://docs.snowflake.com/en/sql-reference/constructs/connect-by.html

SELECT EMP_ID, EMP_NAME, MANAGER_ID FROM EMP_TABLE 
START WITH MANAGER_ID=4
CONNECT BY MANAGER_ID = prior EMP_ID
ORDER BY EMP_ID;

+--------+----------+------------+
| EMP_ID | EMP_NAME | MANAGER_ID |
+--------+----------+------------+
|      1 | A        |          3 |
|      2 | B        |          3 |
|      3 | C        |          4 |
+--------+----------+------------+

SQL Recursive Query to find subordinates under a manager with missing levels

Based on your input, the query is.

; WITH recursive_CTE (id,name,MANAGERID,CEONAME,CEO)
 AS(
 SELECT  ID,name,MANAGERID,name ,ID FROM ASSOSIATETABLE WHERE MANAGERID IS NULL
    UNION  ALL
 SELECT B.ID,B.name,B.MANAGERID,A.CEONAME,A.CEO FROM recursive_CTE  A
 INNER JOIN ASSOSIATETABLE  B ON A.ID=B.ManagerID 
 )

 SELECT * FROM recursive_CTE
 WHERE ID NOT IN (SELECT ID FROM Registration ) 
 AND MANAGERID IS NOT NULL

---

Need the CEO in the result, please remove the "AND MANAGERID IS NOT NULL"

---

---

Fiddler Output

Recursive CTE-Find all Employees Below Manager

Try this. Filter has to be applied in the Anchor query of CTE

WITH UserCTE
     AS (SELECT userId,
                userName,
                managerId,
                0 AS EmpLevel
         FROM   [Users]
         WHERE  managerId = 3
         UNION ALL
         SELECT usr.userId,
                usr.userName,
                usr.managerId,
                mgr.[EmpLevel] + 1
         FROM   [Users] AS usr
                INNER JOIN UserCTE AS mgr
                        ON usr.managerId = mgr.userId
         WHERE  usr.managerId IS NOT NULL)
SELECT *
FROM   UserCTE AS u
ORDER  BY EmpLevel; 

FIDDLE DEMO

---

Related Topics