SQL Error (1111): Invalid use of group function

Question

SQL Error (1111): Invalid use of group function

Navigation

#1 by (1 votes)

1

I want to know the name of the department that has the max number of employees in my database, the query I use is the following:

select depart.dnombre, 
       count(*)
       from emple 
       inner join depart
             on emple.DEPT_NO = depart.DEPT_NO
       group by depart.DNOMBRE
       having count(*) = (select max(count(emple.emp_no))
                                 from emple
                         )

And the error is Invalid use of group function , to see if you could lend me a hand, thanks in advance.

mysql

asked by Rafael Valls 18.03.2018 в 13:45

source

1 answer

Enter fractions textbox C # Local variable 'client' referenced before assignment

score 1 · Answer 1

Most likely, you have the problem here:

having count(*) = (select max(count(emple.emp_no))
                                 from emple
                         )

I do not think that this kind of use of having is valid in MySQL, but beyond that, what you are trying to do is not exactly what you are looking for. This: select max(count(emple.emp_no)) from emple definitely only return the total number of employees, not the department that has more employees.

A simple way to solve your problem could be:

select depart.dnombre, 
       count(*)
       from emple 
       inner join depart
             on emple.DEPT_NO = depart.DEPT_NO
       order by 2 DESC
       limit 1

We get the number of employees per department
We order this amount from highest to lowest
And we limit the output to the first record (the maximum)

Note: The only problem with this is if you have two departments with the maximum number of employees, one of them would return.