SQL - Having aggregation in MySQL

Question

I'm using MySQL and writing this query :

select gauno, count(potno)
from druide_potion
group by gauno
having count(potno) = min(count(potno))

But Mysql says : "#1111 - Invalid use of group function".

In what is this request incorrect? (When I remove the HAVING, I haven't the error but haven't the result expected as well).

Thanks.

Answer 1

In the having clause, each aggregate returns only one value, so requesting the min() of count() makes no sense.

You're probably looking for something like this:

select  *
from    druide_potion
group by
        gauno
having  count(potno) = 
        (
        select  count(potno)
        from    druide_potion
        group by
                gauno
        order by
                 count(potno)
        limit 1
        )

This would return all gauno with the minimum amount of rows with a non-null potno column.