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

What version of SQL are you using (e.g. MySQL, SQL Server, Oracle, etc.) ? We need to know this in order to help you. — Tim Biegeleisen, Nov 11 '19 at 05:34
@Haris `6` is the manager of `7` which means that `6` is also the manager of all the employees that `7` manages and `7` manages two employees. Similarly, `6` is also the manager of `3` which means that `6` is also the manager of all those managed by `3`. So `6` is the manager for `1`, `2`, `3`, `4`, `5` and `7`. — Abra, Nov 11 '19 at 05:59
Which version of MariaDB? There's a big difference between 10.1 and 10.2 — Nick, Nov 11 '19 at 06:00
MariaDB 10.1. By the way, what's the major difference between 10.1 and 10.2? is window function available in 10.2? — JamesWang, Nov 11 '19 at 06:58
@JamesWang yes, plus cte (10.2.1) and recursive cte (10.2.2) — Nick, Nov 11 '19 at 07:24
@JamesWang , Consider upgrading MariaDB to atleast 10.2.2. With `recursive cte` it is very easy to achieve the above result. I can give you the query right away if needed for 10.2.2 and above — Arun Palanisamy, Nov 11 '19 at 08:04
@ArunPalanisamy, please provide the query. Thanks. I'll do the update this evening. thanks — JamesWang, Nov 11 '19 at 17:04
@ArunPalanisamy I have updated to 10.3.17, please provide the sql code so I can try it out. Thanks — JamesWang, Nov 12 '19 at 05:54
@Jameswang, Done. Please check the answer below and let me know if any issues — Arun Palanisamy, Nov 12 '19 at 06:11

Arun Palanisamy · Accepted Answer · 2019-11-12T13:50:10.467

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

awesome! no typo! verified and works like a charm. But I still can't follow completely. I read cte online, and can understand simple case, but this one looks more complicated. don't understand the anchor point, and how it concatenated with recursive part, and why the final select with group by works — JamesWang, Nov 12 '19 at 06:24

nested query to get number of employees under managers

1 Answers1