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

Answer 3

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

This should also work i guess

Answer 4

As explained here you can use

SELECT worker_id, AVG(salary)
FROM workers
GROUP BY worker_id
HAVING AVG(salary) = (SELECT MAX(AVG(salary)) FROM workers GROUP BY worker_id) 

Answer 5

You can fix the query by adding a column alias to the column within the sub-query, like so:

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

However, if worker_id uniquely identifies records on the workers table, this is functionally equivalent to (can be simplified to):

select max(salary) from workers;

Answer 6

using WITH clause it can be done as

with averagesal as (
select dept_id d_id, avg(sal) avgsal from emp_details group by dept_id)
select * from averagesal where avgsal = (select max(avgsal) from averagesal);

Answer 7

select max(a.high)Avg_highest_salary,
       e.dept 
from  (
    select avg(salary) high,dept from emp group by dept) a,
    emp e 
where  a.dept = e.dept
group by   e.dept
order by   max(a.high) desc

It will show the high Average highest salary first with dept

If you don't want to show the Salary with Dept then you can use this

select max(avg(salary)) max_avg_salary
from emp
group by dept;

Answer 8

select Dep_name
from 
(
  select Dep_name , avg(Salary) as avgsal 
  from salary
  group by Dep_name
) sal1
where avgsal=(select  max(avgsal) 
              from (select Dep_name , avg(salary) as avgsal 
                    from salary group by Dep_name) sal2)

Answer 9

You should try the following approach:

select avg(salary) as max_avg_salary from Salaries group by emp_no order by avg(salary) desc limit 1;

Answer 10

https://stackoverflow.com/a/8050885/12190487 shows the folllowing error

ER_DERIVED_MUST_HAVE_ALIAS: Every derived table must have its own alias

Use alias for the new formed column you are selecting from

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

Answer 11

This worked out for me.

from (select avg(salary) AS avg_salary
       from employees
      group by Name) AS T;

Answer 12

You can in this way that the first row is sorted in descending based on average find

select top 1  worker_id, avg(salary) as avgsalary 
      from workers
      group by worker_id 
      order by avgsalary desc

Answer 13

select * from (select avg(sal) over (partition by deptno ) avrg,deptno from emp
order by avrg desc) where rownum<2;

Try the above one.

Answer 14

Here is how to also get the worker_id, inspired by previous answers:

SELECT worker_id, MAX(avg_salary)
FROM (SELECT worker_id, AVG(salary) AS avg_salary
      FROM workers
      GROUP BY worker_id);

Answer 15

You can also do this with a single select statement (combining both max and avg) like this

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