SQLTeam.com | Weblogs | Forums

Count all employee under each manager

Hi All.
I create query to get list of the managers name and thier employees

select
e.EMPLOYEE_ID,
e.EMPLOYEE_NAME EMPLOYEE,
e1.MANAGER_ID,
e1.EMPLOYEE_NAME MANAGER
from Employees e
join Employees e1
on e.MANAGER_ID=e1.EMPLOYEE_ID
group by
e.EMPLOYEE_ID,
e.EMPLOYEE_NAME,
e1.MANAGER_ID,
e1.EMPLOYEE_NAME

Is it possible to modify that query to count number of Employee under each Manager?
Thanks.

; with cte as 
(
SELECT e.employee_id,
       e.employee_name  EMPLOYEE,
       e1.manager_id,
       e1.employee_name MANAGER
FROM   employees e
       JOIN employees e1
         ON e.manager_id = e1.employee_id
GROUP  BY e.employee_id,
          e.employee_name,
          e1.manager_id,
          e1.employee_name 
) 
select manager,count(distinct employee ) from cte group by manager

What do you actually mean by the number of Employee's under each Manager? Do you mean just one level down in the hierarchy or do you mean all the way down to the leaf levels of the hierarchy?