CODEWITHSUNDEEP
mysqlsqlmysql-error-1111
Arnaud F.
Arnaud F.

Reputation: 8452

SQL - Having aggregation in MySQL

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.

Upvotes: 0

Views: 749

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

Proper ANSI query

SELECT D.*
FROM
(
    select min(cnt) MinCount
    FROM
    (
        select gauno, count(potno) cnt
        from druide_potion
        group by gauno
    )
    Counted1
) MinCounted
inner join
(
    select gauno, count(potno) Cnt
    from druide_potion
    group by gauno
) Counted2
    on MinCounted.MinCount = Counted.Cnt
inner join druide_potion D
    ON D.gauno = Counted2.gauno

Upvotes: 0

Imre L
Imre L

Reputation: 6249

select gauno, count(potno)
  from druide_potion
 group by gauno
 order by count(potno)
 limit 1

Upvotes: 0

Andomar
Andomar

Reputation: 238176

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.

Upvotes: 3

Related Questions