nested query to get number of employees under managers

Question

create table employee (
Id int,
ManagerId int);

insert into employee values
(1, 3),
(2, 3),
(3, 6),
(4, 7),
(5, 7),
(6, 8),
(7, 6);

select * from employee;

here is the table:

how to write a query to get managers with number of employees under them. Note the nested structure. the result for above example would be

Id | number_of_employees
3  | 2
7  | 2
6  | 6
8  | 7

Answer 1

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 :)