MYSQL ERROR 1111 (HY000): Invalid use of group function

Question

There are two tables

1. project(project_id, project_name, project_city);
2. assignment(employee_id, employee_name, duration);

Now I have to find in which project maximum number of employee is assigned. I have write a sql query

SELECT MAX(COUNT(employee_id)) from assignment group by project_id;

but this query is giving the following error:

ERROR 1111 (HY000): Invalid use of group function. I am using mySql.

Answer 1

The simplest way to get what you want is order by and limit:

SELECT COUNT(employee_id) as cnt
FROM assignment 
GROUP BY project_id
ORDER BY cnt DESC
LIMIT 1;