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.
15 Answers
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;
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
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)
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
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;
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);
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;
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)
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;
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 ;
This worked out for me.
from (select avg(salary) AS avg_salary
from employees
group by Name) AS T;
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
select * from (select avg(sal) over (partition by deptno ) avrg,deptno from emp
order by avrg desc) where rownum<2;
Try the above one.
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);
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;
