I got a problem with one task.
I need to count all subordinates (distinct) who directly or indirectly reports to a specific manager
I have an Employee
table like this:
EMPLOYEE_ID Int,
MANAGER_ID Int,
EMPLOYEE_NAME varchar(200)
Example:
Alex(1)
--------------------
Jhon(2) Kevin(3)
------------------------------
Mike(4) Amanda(5) Tom(6) Jery(7)
I can count only employee who directly reports to a manager:
SELECT
MANAGER_ID
,COUNT(MANAGER_ID) as SubCount
FROM [dbo].[EMPLOYEE]
GROUP BY MANAGER_ID
But in result I have something like this:
Manager_ID | SubCount
----------------------
1 | 2
2 | 2
3 | 2
----------------------
Instead:
Manager_ID | SubCount
----------------------
1 | 6
2 | 2
3 | 2
----------------------
I will be happy for any suggestions or idea how to do this.
declare @t table(EMPLOYEE_ID Int,
MANAGER_ID Int,
EMPLOYEE_NAME varchar(200))
insert @t values(1,null,'Alex'),(2,1,'Jhon'),(3,1,'Kevin'),
(4,2,'Mike'),(5,2,'Amanda'),(6,3,'Tom'),(7,3,'Jerry')
;with a as
(
select EMPLOYEE_ID boss,EMPLOYEE_ID from @t t
where exists (select 1 from @t where t.EMPLOYEE_ID = MANAGER_ID)
union all
select a.boss, t.EMPLOYEE_ID
from @t t join a on t.MANAGER_ID = a.EMPLOYEE_ID
)
--subtracting 1 because it is also counting the manager
select boss, count(*)-1 SubCount from a group by boss
option (maxrecursion 20)
You need to use a recursive CTE for this:
with managers as (
select employee_id, manager_id, 1 as level
from employees e
union all
select e.employee_id, m.manager_id, e.level+1
from managers m join
employees e
on m.manager_id = e.employee_id
)
select m.manager_id, count(*)
from managers m
group by m.manager_id;
The recursive CTE creates all employee/manager pairs. The final query does the aggregation and counting.
EDIT:
It sounds like there are cycles in the employee/manager relationship. I think the following fixes this (I don't have SQL Server available right now to test it):
with managers as (
select employee_id, manager_id, 1 as level
from employees e
union all
select e.employee_id, m.manager_id, e.level+1
from managers m join
employees e
on m.manager_id = e.employee_id
where e.employee_id not in (select employee_id from managers)
)
select m.manager_id, count(*)
from managers m
group by m.manager_id;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With