How to resolve \"Invalid use of group function\" error in MySQL when finding maximum record count?

Finding Maximum Record Count in MySQL

In MySQL, when attempting to find the maximum record count using the max(count(*)) aggregation function, you may encounter the error "Invalid use of group function." To resolve this issue, it's necessary to modify the query to correctly determine the maximum count.

In the provided query:

select max(count(*)) from emp1 group by name;

The group function count(*) is used within the max function, resulting in the error. To fix this, you can modify the query as follows:

SELECT NAME, 
       COUNT(*) as c 
FROM table 
GROUP BY name 
ORDER BY c DESC LIMIT 1

This query will first calculate the count of records for each unique value in the name column and assign the count to a new column named c. It then groups the results by the name column, sorts them in descending order based on the c column, and retrieves only the first row, which contains the maximum count for any unique name.

The above is the detailed content of How to resolve \"Invalid use of group function\" error in MySQL when finding maximum record count?. For more information, please follow other related articles on the PHP Chinese website!