How to Find the Maximum Count in MySQL Grouped by a Column?

Finding Maximum Count in MySQL

When attempting to find the maximum count of records in a MySQL table using the max(count(*)) aggregation function grouped by a specific column, you may encounter the error: "Invalid use of group function." This error occurs because the count(*) function is an aggregate function that cannot be used within another aggregate function like max.

To resolve this issue and obtain the maximum count of records grouped by a particular column, you can use the following alternative approach:

<code class="sql">SELECT name, COUNT(*) AS count_of_name
FROM table_name
GROUP BY name
ORDER BY count_of_name DESC
LIMIT 1</code>

In this query:

• name is the column you want to group the results by.
• COUNT(*) counts the number of rows in each group and aliases the result as count_of_name.
• GROUP BY name groups the results by the name column.
• ORDER BY count_of_name DESC orders the results in descending order of count_of_name.
• LIMIT 1 retrieves only the first row, which represents the group with the maximum count.

The above is the detailed content of How to Find the Maximum Count in MySQL Grouped by a Column?. For more information, please follow other related articles on the PHP Chinese website!