Query to calculate Average of Employee Salaries working under manager in SQL

Question

Below is the Employee table which includes manager details as well. The task is to calculate the average salary under manager:

emp_id      emp_name     salary       manager_id
----------- --------     ---------   -------------
10          Anil         50000.00     18
11          Vikas        75000.00     16
12          Nisha        40000.00     18
13          Nidhi        60000.00     17
14          Priya        80000.00     18
15          Mohit        45000.00     18
16          Rajesh       90000.00     NULL
17          Raman        55000.00     16
18          Santosh      65000.00     17

I have written below query:

SELECT e1.emp_id as manager_id, 
       e1.emp_name as manager_name, 
       avg(e2.salary) as employee_avg_salary
FROM employee e1 inner join 
     employee e2
ON e1.manager_id = e2.emp_id
GROUP BY e1.emp_id, e1.emp_name
ORDER BY e1.emp_id

which is wrong as per solution set.

My thinking was I'm doing self-join on the employee table on condition manager_id from e1 is equal to employee id from e2 so grouped by e1.emp_id and e1.emp_name since left side contains manager set.

The current solution seems to be

select e2.emp_id as "Manager_Id",
          e2.emp_name as "Manager", 
          avg(a.salary) as "Average_Salary_Under_Manager"
from Employee e1, 
     Employee e2
where e1.manager_id = e2.emp_id
group by e2.emp_id, e2.emp_name
order by e2.emp_id;

The result set should be

ManagerId   ManagerName     AverageSalary
-----------------------------------------
    16          Rajesh          65000
    17          Raman           62500
    18          Santosh         53750

    (3 rows affected)

Could somebody please explain the logic why is it so, I'm asking only for the explanation.

Answer 1

What we are basically doing here is self joining and grouping by the manager from E2 who tends to repeat for every manager_id entry. And we are retrieving the employee id and name for each entry in E2 after group by and we calculate the Average of salaries all the entries from E1

SELECT E2.EMP_ID, E2.EMP_NAME, AVG(E1.SALARY)
FROM MANAGER_EMP E1
INNER JOIN MANAGER_EMP E2
ON E1.MANAGER_ID = E2.EMP_ID
GROUP BY E2.EMP_ID, E2.EMP_NAME

The result set is

EMP_ID      EMP_NAME                                           EMP_AVG_SALARY
----------- -------------------------------------------------- ---------------
16          Rajesh                                             65000.000000
17          Raman                                              62500.000000
18          Santosh                                            53750.000000

(3 rows affected)

Answer 2

As there is no desired result set.

Try below query:

SELECT AVG(e1.Salary) as AvgSalary, e1.Manager_id , e2.emp_name as ManagerName 
      FROM employee e1
      INNER JOIN employee e2 ON e1.Manager_id = e2.emp_id
      WHERE e1.Manager_id IS NOT NULL
      GROUP BY e1.Manager_id , e2.emp_name

Answer 3

To calculate the average salary under a manager first you need to know the employee's who work under a manager and their respective salary.

First, you need to map all the employees under a manager so that you can arrive at the sum.

In the query you have put up in you question,the table is joined to itself using a manager id(this is called self-join). Explaining using

emp_id      emp_name     salary       manager_id
----------- --------     ---------   -------------
10          Anil         50000.00     18
11          Vikas        75000.00     16
12          Nisha        40000.00     18
13          Nidhi        60000.00     17
14          Priya        80000.00     18
15          Mohit        45000.00     18
16          Rajesh       90000.00     NULL
17          Raman        55000.00     16
18          Santosh      65000.00     17

Rajesh is a manager who has Vikas and Raman under him. So,you need to find the employee's who has the manager_id=16.

When you use the query in your question then join condition looks for the above condition.It grabs all the employees and their salary who are under one manager.Sums it up and groups it by the manager id.

Also,there is a mistake in the column alias in you query.Use the query from @ravi's answer

Check this if you're still not clear on how self join works Explanation of self-joins

Simple fiddle if you want to check this,fiddle_example

Answer 4

Thanks @theDbGuy for tips.

create table #avg (emp_id int, emp_name varchar(20),salary int,manager_id int)

insert #avg values
 (10,'Anil',   50000.00,18)
,(11,'Vikas',  75000.00,16)
,(12,'Nisha',  40000.00,18)
,(13,'Nidhi',  60000.00,17)
,(14,'Priya',  80000.00,18)
,(15,'Mohit',  45000.00,18)
,(16,'Rajesh', 90000.00,NULl)
,(17,'Raman',  55000.00,16)
,(18,'Santosh',65000.00,17)

select am.emp_id ManagerId, am.emp_name ManagerName
, avg(ae.salary) AverageSalary from #avg aM     --Manager 
join #avg aE -- Employee
on am.emp_id = aE.manager_id
group by am.emp_id, am.emp_name
order by am.emp_id

ManagerId   ManagerName     AverageSalary
-----------------------------------------
    16          Rajesh          65000
    17          Raman           62500
    18          Santosh         53750

Answer 5

SELECT a.Manager_Id,b.Emp_name as manager ,avg(a.salary) as Average_Salary_Under_Manager 
FROM employee a 
INNER JOIN employee b ON a.Manager_Id = b.Emp_Id 
GROUP BY a.Manager_Id,b.Emp_name