I have the relation instructor(ID, name, dept_name, salary).
How would I go about finding the name of the department with the highest average salary?
Something along the lines of:having avg(salary) > ?
Doug
2010-02-02 15:46:40
+1
A:
will this do the trick?
select top 1 id, name, avg (salary)
from instructor
group by id, name
order by avg (salary) desc
Raj More
2010-02-02 15:35:17
No. I can get the averages of all the department's by using:select dept_name, avg(salary)from instructorgroup by dept_nameBut I can't select the highest average salary department from there.
Doug
2010-02-02 15:41:15
select dept_name from instructorgroup by dept_nameorder by avg(salary) desc limit 1 Gives me an error "SQL command not properly ended"
Doug
2010-02-02 15:52:43
I am using Oracle SQL, however trying to use having rownum=1 still results in the same error.
Doug
2010-02-02 16:08:22
top 1 is TSQL; limit 1 is used in MySQL. My earlier comment with "having rownum=1" is incorrect, see http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html for the full details. If you are using Oracle, you will have to do something like: select * from (select dept_name, avg(salary) from instructor group by dept_name order by avg(salary)) where ROWNUM = 1
James
2010-02-02 16:13:30
A:
Given the homework tag, I won't spell it out for you, but you want to look into the AVG function and the GROUP BY clause.
David Hedlund
2010-02-02 15:35:42
It is indeed homework, my mistake for not tagging it as such, but I've been working on it quite a bit and just can't figure it out.
Doug
2010-02-02 15:37:22
A:
select top 1 dept_name, avg(salary) as AvgSalary
from instructor
group by dept_name
order by AvgSalary desc
RedFilter
2010-02-02 16:01:34
A:
This will get you both if two deparments have the same average salary, use rownum=1 if this is not needed.
with averages as (select dept_name,avg(salary) aver from instructor group by dept_name)
select dept_name
from averages
where aver = (select max(aver) from averages)
John Ormerod
2010-02-02 17:33:42