CODEWITHSUNDEEP
mysqlsqldatabasemax
user3503781
user3503781

Reputation:

Find the dept that is being paid the max salary

I'm a student and am working on a MySql assignment given to us for the holidays.
I have a table that looks something like this :

name      dept     salary  
 A       Sales      100
 B       Marketing  200
 C       Sales      800

(Sorry I'm new to stackexchange so I don't know how to display a table.)

The question for the query is : find the dept that is being paid the max salary.
I entered the following query :

SELECT dept
     , SUM(salary)
FROM emp
GROUP BY dept
HAVING MAX(SUM(salary));

But I am getting the following error:

'Invalid use of group function error'.

Upvotes: 3

Views: 146

Answers (1)

potashin
potashin

Reputation: 44581

In case salary sum is unique, you can calculate sum for each dept then order records in descending order by aggregated salary and fetch the first record (with the greatest salary):

select dept
     , sum(salary) as salary
from tbl
group by dept
order by salary desc
limit 1

In case salary sum can be the same for multiple depts you can calculate salary sum for each dept , then find maximum salary sum the same way as explained above and using a having clause validate if salary sum for each group is equal to the maximum salary sum:

select dept
from tbl
group by dept 
having sum(salary) = ( select sum(salary) as salary
                       from tbl
                       group by dept
                       order by salary desc
                       limit 1)

Upvotes: 2

Related Questions