bqui56 bqui56 - 5 months ago 17
SQL Question

How to find maximum avg

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

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);