MySQL: Why are these two wrong? (Invalid use of group function, selected nothing after using having())

Credit:Leetcode_1076.Project Employees II Here is the sample table Project table:

 project_id  | employee_id 

 1           | 1           
 1           | 2           
 1           | 3           
 2           | 1           
 2           | 4           

(project_id, employee_id) is the primary key of this table.

I have tried several methods and managed to find the right one by using dense_rank(); however, I still don’t understand why these two syntaxes are wrong to select project_id with the largest number of employees:

1.Return null: {“headers”: [“project_id”, “n”], “values”: []}

select project_id, count(*) as n
from project
group by project_id
having max(n)

• I was expecting it to return {“headers”: [“project_id”, “n”], “values”: [1,3]}.

2. Error: Invalid use of group function

SELECT project_id, max(count(*)) as n
from project 
group by project_id

Really appreciate it if anyone can help me out!!

Answer

There is one common issue with both of your approaches, and that is, you are attempting to access aggregated result in the SQL at same level. You can not have nested aggregate functions (Eg. max(count(1)) in SQL.

Your SQL should be as follows –

select t.project_id, max(t.n) from (select project_id, count(*) as n
from project
group by project_id) t;

SQLFiddle demonstration – http://sqlfiddle.com/#!9/9c9b18/4