How to find maximum avg

Question

I am trying to display the maximum average salary; however, I can't seem to get it to work.

I can get a list of the average salaries to display with:

select worker_id, avg(salary)
from workers
group by worker_id;

However, when I try to display a list of the maximum average salary with:

select max (avg(salary))
from (select worker_id, avg(salary)
      from workers
      group by worker_id);

it doesn't run. I get an "invalid identifier" error. How do I use the average salary for each worker to find the maximum average for each worker?

Thanks.

Answer 1

Columns resulting from aggregate functions (e.g. avg) usually get arbitrary names. Just use an alias for it, and select on that:

select max(avg_salary)
from (select worker_id, avg(salary) AS avg_salary
      from workers
      group by worker_id) As maxSalary;

Answer 2

select worker_id, avgsal 
from 
(
  select worker_id, avg(salary) as avgsal 
  from workers 
  group by worker_id
) 
where avgsal=(select  max(avgsal) 
              from (select worker_id, avg(salary) as avgsal 
                    from workers group by worker_id))

This will display the highest average along with worker id