How to Retrieve the Maximum Count Using MySQL

In MySQL, you may encounter an issue while attempting to find the maximum count of values grouped by a specific column using the following command:

mysql> select max(count(*)) from emp1 group by name;
ERROR 1111 (HY000): Invalid use of group function

Understanding the Error

The error arises because MySQL does not allow using aggregate functions like max and count together as arguments within another function. Instead, group functions like count must be used directly in the GROUP BY clause.

Correct SQL Query

To correctly find the maximum count of values grouped by the name column, use the following modified query:

SELECT name,
       COUNT(*) AS c
FROM emp1
GROUP BY name
ORDER BY c DESC
LIMIT 1

Query Explanation

• The SELECT statement extracts the name column and the count of each name as an alias c.
• The GROUP BY name clause groups the results based on the name column.
• The ORDER BY c DESC clause sorts the results in descending order based on the count values.
• The LIMIT 1 clause fetches only the first row with the maximum count value.