SELECT AVG(salary) FROM emp where COUNT(DISTINCT(dept)) > 3 ;
使用这个查询,我想计算员工人数超过3的部门的平均工资。
我想计算员工人数超过3的部门的平均工资。
当我解释这个问题时,你想要:
select dept, avg(salary)
from emp
group by dept
having count(*) > 3;
如果你想要所有部门的平均值,那么不清楚你是否想要部门平均值的平均值。或者这些部门员工的平均水平。无论哪种方式,您都需要一个子查询:
select avg(dept_avg) as average_of_department_salaries,
sum(sum_salary) / sum(num_emp) as employee_average_salary
from (select dept, avg(salary) as dept_avg, count(*) as num_emp, sum(salary) as sum_salary
from emp
group by dept
having count(*) > 3
) d
您可以使用HAVING:
SELECT AVG(salary)
FROM emp
HAVING COUNT(DISTINCT dept) > 3 ;
我不确定你到底需要什么,但这里是我的例子的演示,还有一个例子,里面有一个函数demo
以上是Oracle的答案。对于sqlserver,我建议使用";函数内部的函数";像这样:
select avg(tolumn1)
from (select count(salary) as tolumn1
from emp) A
Select Avg(E.Salary),D.Department_name from Departments AS D inner join Employee AS E on D.Dept_Id = E.Dept_Id group by Department_name having count(Emp_Id)>3
如果没有Department表和Employee表的联接,就无法获得问题所需的结果。因此,对这两个表使用别名,后面跟着Groupby子句和Having子句。