who pays the highest average salary to his employees?

Question

Have two tables:

Employee

e_id | e_name | m_id | d_id | salary | bonus
  1     Andi     3      B     56000    10000
  2     Sam      4      A     24000
  3     Dave            A     89000    23000
  4     Mike     3      C     62000    15000
  5     Red      1      B     42000
  6     Don      1      C     37000
  7     Bill     4      C     39000

and

Department

d_id | d_name | mg_id | location
 A       Dep1     3       US
 B       Dep2     1       DE
 C       Dep3     4       RU

m_id in Department shows the e_id who is managing the department; mg_id in Employee show the e_id who is a manager of the corresponding employee.

I find the managers using the following query:

SELECT Employee.e_name
  FROM Employee
    INNER JOIN Department
    ON Employee.e_id = Department.mg_id
    AND Employee.d_id = Department.d_id;

Question is how to find which manager pays the highest average salary (+ bonus) to his employees?

Unknown Unknown · Accepted Answer · 2016-12-05T14:54:25

You can ...

SELECT DeptHead.e_name, average(Employee.salary)
  FROM Department
   INNER JOIN Employee
       ON Employee.d_id = Department.d_id
   INNER JOIN Employee as DeptHead
       ON DeptHead.e_id = Department.mg_id
 GROUP BY DeptHead.e_name
 ORDER BY average (Employee.salary) DESC
  LIMIT 1;

This joins the department to the employee table twice, once to get the department head's information, and another to get the employee information. Because of this, one of those has to be "aliased". For the employee information, you group by it and use an aggregate function to get the average salary. By ordering descending, you get the highest first, and by limiting to just the first record, you get only the first = the highest.

who pays the highest average salary to his employees?

1 Answers