Find the department that is paid the maximum salary
I am a student working on a MySql assignment we give 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 don't know how to display the table.)
Question for inquiry: find the department that is paid the maximum 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.
source to share
In case the amount is salary
unique, you can calculate the amount for each dept
and then write the records in descending order by the aggregated salary and get the first record (with the highest salary):
select dept
, sum(salary) as salary
from tbl
group by dept
order by salary desc
limit 1
In case the amount salary
may be the same for several dept
, you can calculate the salary amount for each dept
, then find the maximum amount salary
in the same way as described above, and using the having
validate clause if the amount salary
for each group is equal to the maximum amount salary
:
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)
source to share