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?