计算SQL的平均问题

由也，許发布于 2020-05-09 22:08:36 MySQLsqlcountaverage

我有一张桌子，上面有工人及其工资。我试算有多少工人的薪水高于平均水平我知道如何显示平均值，我知道如何计算公司有多少工人但是我没有回答这个问题。这是我尝试过的但出现错误：

SELECT COUNT(workers_id) FROM flight_company.workers
WHERE Salary > AVG(Salary);

xsit 2020-05-09 22:08:36 回复

如果您正在运行MySQL 8.0，请使用窗口函数：

select avg_salary, count(*) no_workers_above_average
from (select salary, avg(salary) over() avg_salary from flight_company.workers) t
where salary > avg_salary
group by avg_salary

在早期版本中，一个选项是使用汇总查询进行联接：

select a.avg_salary, count(*) no_workers_above_average
from flight_company.workers w
inner join (select avg(salary) avg_salary from flight_company.workers) a
where w.salary > a.avg_salary
group by a.avg_salary

oqui 2020-05-09 22:08:36 回复

您可以使用子查询：

select count(w.workers_id))
from flight_company.workers w
where salary > (select avg(salary) from flight_company.workers);

菊花残 2020-05-09 22:08:36 回复
You can do it with window function avg() (MySql 8.0+) and aggregation:
```
select sum(t.flag)
from (select salary > avg(salary) over () flag from workers) t
```
寂寞在掉泪 2020-05-09 22:08:36 回复

这个为我工作

SELECT COUNT(workers_id) as Num_Above_Average FROM flight_company.workersWHERE Salary > (select avg(salary) from flight_company.workers)

如何将“平均工资”列添加到此？

谢谢