Search code examples
mysqlsqlgroup-byaveragegreatest-n-per-group

SQL - Greatest-n-per-group of the average

I am having trouble with the following query. Lets say you have the following table of employees:

Example table EMPLOYEE:

IDEMP |    JOB    | SALARY | CONTENT 
------+-----------+--------+---------
  1   |   CLERK   |  1500  |   ...   
  2   |   CLERK   |  1000  |   ...   
  3   | PRESIDENT |  5000  |   ...   
  4   |  ANALYST  |  2000  |   ...   
  ...       ...       ...      ...

I am trying to obtain the JOB that has the highest average salary. I can easily obtain the average salary of each JOB with this subquery:

(select job, avg(salary) as AVERAGE 
 from emp
 group by job) temp

However, I don't know how I can obtain after this subquery the max(AVERAGE) with the associated JOB. I know that if I only needed the row of the employee with the highest salary, I could do it as explained here SQL select only rows with max value on a column, but when you do the 'average subquery' you lose the IDs of the employees.

Since for this given example there is only one PRESIDENT, and the job has the highest average salary, the output should be this:

   JOB    |  MAX_AVERAGE 
----------+---------------
PRESIDENT |     5000

EDIT: My solution...

Thanks for the help, you brought up new ideas. I'm finally using this one because it does not limit the results to one row.

select e.job, avg(e.salary) as AVERAGE 
 from EMPLOYEE e
 group by e.job
 having avg(e.salary) >= ALL(select avg(salary) from EMPLOYEE group by job)
Solution

  • I've figured out one query that returns my desired result and is also more flexible than the previously ones posted since it can return multiple rows if it was the case:

    select e.job, avg(e.salary) as AVERAGE 
 from EMPLOYEE e
 group by e.job
 having avg(e.salary) >= ALL(select avg(salary) from EMPLOYEE group by job)