SQLTeam.com | Weblogs | Forums

How to get number of employees in the department and manager per employee

#1

Hi All.
I have Employees table
[Employee_ID] ,
[Employee_Name],
[Gender],
[Title],
[Manager_ID],
[Hire_Date],
[Salary],
[Commission],
[Department_ID]

I created query to get number of employees in the department and manager per employee.

SELECT
e.EMPLOYEE_ID,
e.EMPLOYEE_NAME,
e.MANAGER_ID,
d.EmpCount
FROM EMPLOYEES e
JOIN (SELECT DEPARTMENT_ID, COUNT(EMPLOYEE_ID) as EmpCount
FROM Employees
GROUP BY DEPARTMENT_ID) d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID

How to get Manager_Name instead Manader_ID which is part of employees?

Thanks

#2

Can you provide sample data and expected results? If I am looking at this correctly - you should be able to do something like this:

 Select e.EMPLOYEE_ID
      , e.EMPLOYEE_NAME
      , ManagerName = e.EMPLOYEE_NAME
      , EmpCount = count(e.EMPLOYEE_ID) over(Partition By e.DEPARTMENT_ID)
   From EMPLOYEES       e
  Inner Join EMPLOYEES  m On m.EMPLOYEE_ID = e.MANAGER_ID;
#3

Hi jeffw8713. Thanks for reply.
In the result of your select every employee is manager. It is not correct. Should be some of employees are manager but not every one. My select is correct. Only I would like to have in the result Manager_Name instead Manager_Id. In that place I have problem.

Thanks.

#4

Sorry change the manager name to m.employee_name

#5

m.ManagerName doesn't work. It gives error - multi-part identifier. I solve the problem

Select e.EMPLOYEE_ID
, e.EMPLOYEE_NAME
, m.EMPLOYEE_NAME Manager_Name
, EmpCount = count(e.EMPLOYEE_ID) over(Partition By e.DEPARTMENT_ID)
From EMPLOYEES e, EMPLOYEES m
where e.Manager_ID=m.Employee_ID

Now interesting how to get same result but avoid Managers from Employee_Name column?

Thanks

#6

I did not say to use m.ManagerName - I told you to replace the manager name with m.EMPLOYEE_NAME.

You also need to use standard join syntax - as putting the join criteria in the where clause can be harder to troubleshoot and diagnose if you have further issues.

If you want to exclude managers - you need to be able to identify a manager as opposed to an employee only. I would assume that a manager does not have a manager ID in the employee table - so just add criteria to exclude them.

#7

Hi. Thanks for continue to help.
Can you show code in context of your idea?

Thanks

#8

Same code I posted before...just add a where clause to exclude managers. I don't know the data in your table so I cannot say what denotes a manager as opposed to an employee.

 Select e.EMPLOYEE_ID
      , e.EMPLOYEE_NAME
      , ManagerName = m.EMPLOYEE_NAME
      , EmpCount = count(e.EMPLOYEE_ID) over(Partition By e.DEPARTMENT_ID)
   From EMPLOYEES       e
  Inner Join EMPLOYEES  m On m.EMPLOYEE_ID = e.MANAGER_ID
  Where e.MANAGER_ID IS NULL  -- or maybe e.MANAGER_ID = 0???

But - if you are looking for a count of employees per department, the manager is also an employee in that department. Generally - a manager would also report up to his/her manager/director (another employee).

#9

hi

this same question of yours is there since 20 years

please google search .. lots lots lots of answers

here is one link