How to use MAX function in MySQL with the having clause-CodePudding

I am working with the employees table in SQL and I would like to fetch the data for max count of employees

SELECT (COUNT(emp_no)) AS emp_count, dept_no 
FROM
dept_emp
GROUP BY dept_no
HAVING COUNT(emp_no) = (SELECT MAX(COUNT(emp_no)) FROM dept_emp)
ORDER BY emp_count DESC

So far this is what I have got but this results in an error saying 'Invalid use of group function'. There is another approach I followed by making a table first and then using the having clause but what would be the correct code in the above approach?

CodePudding user response：

You don't have to use the having at all. The query as it is without the having will bring you all the departments with the number of employees at each one. The one with the most employees in the first row. If you want only that one you can add limit 1 at the end of the query.

CodePudding user response：

You can't not use an aggregation over an aggretation, MAX(COUNT()) is invalid

SELECT (COUNT(emp_no)) AS emp_count, dept_no 
FROM dept_emp
GROUP BY dept_no
HAVING COUNT(emp_no) = ( 
    SELECT MAX(count_result) FROM (SELECT COUNT(emp_no) as count_result FROM dept_emp) as count_table
)
ORDER BY emp_count DESC

Side notes:

I think there is a missing WHERE in the subquery as the result will be always the same, as we are getting the MAX of a COUNT to a unfiltered table dept_emp
I think the MAX(COUNT()) is irrelevant in the subquery, since you can just order by the count and limit by one, for example SELECT COUNT(id) FROM foo ORDER BY COUNT(id) DESC LIMIT 1
If you can avoid the subqueries, databases are incredible slow understanding subqueries, if you are curious prepend EXPLAIN to the sql statement, and see what mysql does for it

Edit: If you provide the output of SHOW CREATE TABLE table_name for the tables that are involved in employee counting, I can give you the WHERE that you have to write in the subquery