CODEWITHSUNDEEP
sqlmaxaverage
Doug
Doug

Reputation: 13

SQL finding the maximum of all averaged results

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?

Upvotes: 1

Views: 4303

Answers (5)

John Ormerod
John Ormerod

Reputation: 156

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)

Upvotes: 0

Raj More
Raj More

Reputation: 48016

will this do the trick?

select top 1 id, name, avg (salary)
from instructor
group by id, name
order by avg (salary) desc

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

select top 1 dept_name, avg(salary) as AvgSalary
from instructor
group by dept_name
order by AvgSalary desc

Upvotes: 0

David Hedlund
David Hedlund

Reputation: 129792

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.

Upvotes: 0

Silvio Donnini
Silvio Donnini

Reputation: 3303

looks like a job for the HAVING clause

Upvotes: 3

Related Questions