SQL 如何找到最大平均值

How to find maximum avg

提问by bqui56

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.

谢谢。

回答by king_nak

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

由聚合函数（例如 avg）产生的列通常具有任意名称。只需为它使用别名，然后选择：

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

回答by Mathews

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

这将显示最高平均值和工人 ID

回答by Vaibhav Kedia

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

我想这也应该有效

回答by Anand

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)

回答by vks

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;

回答by srishti

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

https://stackoverflow.com/a/8050885/12190487显示以下错误

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 ; 

回答by Meghna Sharma

This worked out for me.

这对我有用。

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

回答by Tajuddin Mulimani

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

Try the above one.

试试上面的那个。

回答by Dineesh A V

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'twant to show the Salary with Deptthen you can use this

如果您不希望显示与部门工资，那么你可以使用这个

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

回答by Dheerajs83

using WITH clause it can be done as

使用 WITH 子句可以这样做

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