Search code examples
mysqlsqlselecttop-n

I have to find the the name of the department with maximum average from the table salary

This is the code I am writing and getting error

select Dep_name,T
from
(select Dep_name,avg(salary) as T
from salary
group by Dep_name) as TT
having max(T);

error: ERROR 1140 (42000) at line 4: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'TT.Dep_name'; this is incompatible with sql_mode=only_full_group_by

Solution

  • One approach is to order by the average salary and take the first row only by using a limit clause:

    SELECT   dep_name, AVG(salary) 
FROM     salary 
GROUP BY dep_name 
ORDER BY 2 DESC
LIMIT    1