CODEWITHSUNDEEP
phpmysqlsql
Martin C
Martin C

Reputation: 395

Find maximum average in sql

I am trying to get the maximum average salary and i am using this:

select max (avg_salary)
from (select name, avg(salary) AS avg_salary
  from employee
  group by name);

But is another way to get same result without using subquery?

Upvotes: 0

Views: 288

Answers (5)

Justin Paul Paño
Justin Paul Paño

Reputation: 936

All employees:

SELECT name, 
       AVG(salary) AS avg_salary 
FROM employee 
GROUP BY name 
ORDER BY avg_salary DESC

Maximum of one employee:

SELECT name, 
AVG(salary) AS avg_salary 
FROM employee 
GROUP BY name 
ORDER BY avg_salary DESC 
LIMIT 1

Upvotes: 0

M Shahzad Khan
M Shahzad Khan

Reputation: 935

Use this instead

SELECT name, 
       avg(salary) AS avg_salary   
FROM employee 
GROUP BY name 
ORDER BY DESC avg_salary 
      LIMIT 1

Upvotes: 0

naveen goyal
naveen goyal

Reputation: 4629

Try this... this sort desc of avg salary then apply limit 

SELECT AVG(salary),name
FROM employee GROUP BY name
ORDER BY AVG(salary) DESC
LIMIT 1

Upvotes: 0

fthiella
fthiella

Reputation: 49049

SELECT AVG(salary)
FROM employee
GROUP BY name
ORDER BY AVG(salary) DESC
LIMIT 1

Upvotes: 2

Filipe Silva
Filipe Silva

Reputation: 21657

One other option would be:

SELECT name, avg(salary) AS avg_salary
FROM employee
GROUP BY name
ORDER BY 2 DESC LIMIT 1;

Upvotes: 1

Related Questions