Avg Salaries under current managers

user_15446912 0
Viewed 35

I have this table: Employee

emp_id emp_name salary manager_id
1 emp_1 5000 7
2 emp_2 3600 6
3 emp_3 2500 7
4 emp_4 4600 6
5 emp_5 9000 NULL
6 emp_6 7500 5
7 emp_7 2300 6

I want to get the Average salary for the employees under manager

Here is an example of the result I want:

emp_id emp_name AVG_Salary
5 emp_5 7500
6 emp_6 3500 //which is (3600 + 4600 + 2300) / 3;
7 emp_7 3750 //which is (5000+ 2500) / 2;

Can you please tell me how to figure out what I need to get the query please.

mysql
1 Answers

Here is the solution:

SELECT e2.emp_id AS "Manager_ID", e2.emp_name AS "Manager_Name", AVG(e1.salary) AS "AVG" 
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;

Description:
We want to join on the same table in this problem and we want to select the managers who are (5-6-7) in this case (e2) and the case is that e1 manager id is what we want, and to get the avg we use AVG function on the employees salaries (e1) in this case and we use GROUP BY to collect the avg and ORDER BY to order it.

user_151533260
Related