Count all subordinates who directly or indirectly reports to managers

sql, sql-server-2008, t-sql

Solution

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)

Problem

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.

Original source