Grokbase Groups MySQL mysql May 2005
FAQ

[MySQL] ERROR 1111 (HY000): Invalid use of group function

Shaun thornburgh
May 11, 2005 at 6:36 pm
Hi,

I keep getting thw following error when i include this line:

AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1

mysql> SELECT PRACT_NUMBER,
-> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
-> FROM CSV_Upload_Data
-> WHERE CSV_File = 'ICS'
-> AND CHAR_LENGTH(PRACT_NUMBER) > 4
-> AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
-> AND Booking_ID = 6030
-> GROUP BY PRACT_NUMBER;
ERROR 1111 (HY000): Invalid use of group function
mysql>

Can anyone tell me why this is happening?

Thanks for your advice
reply

Search Discussions

3 responses

  • Paul DuBois at May 11, 2005 at 6:40 pm

    At 18:36 +0000 5/11/05, shaun thornburgh wrote:
    Hi,

    I keep getting thw following error when i include this line:

    AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1

    mysql> SELECT PRACT_NUMBER,
    -> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
    -> FROM CSV_Upload_Data
    -> WHERE CSV_File = 'ICS'
    -> AND CHAR_LENGTH(PRACT_NUMBER) > 4
    -> AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
    -> AND Booking_ID = 6030
    -> GROUP BY PRACT_NUMBER;
    ERROR 1111 (HY000): Invalid use of group function
    mysql>

    Can anyone tell me why this is happening?
    Because you cannot use aggregate functions in a WHERE clause.

    WHERE determines which rows to select.

    Aggregate values are calculated from the selected rows.

    --
    Paul DuBois, MySQL Documentation Team
    Madison, Wisconsin, USA
    MySQL AB, www.mysql.com
  • Michael Stassen at May 11, 2005 at 6:51 pm

    Paul DuBois wrote:
    At 18:36 +0000 5/11/05, shaun thornburgh wrote:

    Hi,

    I keep getting thw following error when i include this line:

    AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1

    mysql> SELECT PRACT_NUMBER,
    -> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
    -> FROM CSV_Upload_Data
    -> WHERE CSV_File = 'ICS'
    -> AND CHAR_LENGTH(PRACT_NUMBER) > 4
    -> AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
    -> AND Booking_ID = 6030
    -> GROUP BY PRACT_NUMBER;
    ERROR 1111 (HY000): Invalid use of group function
    mysql>

    Can anyone tell me why this is happening?
    Because you cannot use aggregate functions in a WHERE clause.

    WHERE determines which rows to select.

    Aggregate values are calculated from the selected rows.
    so you have to move this condition to the HAVING clause:

    SELECT PRACT_NUMBER,
    COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
    FROM CSV_Upload_Data
    WHERE CSV_File = 'ICS'
    AND CHAR_LENGTH(PRACT_NUMBER) > 4
    AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
    AND Booking_ID = 6030
    GROUP BY PRACT_NUMBER
    HAVING COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1;

    Michael
  • Stefan Kuhn at May 11, 2005 at 6:41 pm
    I would guess it's because you can't use an aggregate function in where, but
    only in having. So use select .... group by PRACT_NUMBER having
    COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1.
    The reason is that where is applied before the count is done, whereas having
    after that. And you can't select by something which you will know after you
    have done the select.
    Stefan

    Am Wednesday 11 May 2005 20:36 schrieb shaun thornburgh:
    Hi,

    I keep getting thw following error when i include this line:

    AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1

    mysql> SELECT PRACT_NUMBER,
    -> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
    -> FROM CSV_Upload_Data
    -> WHERE CSV_File = 'ICS'
    -> AND CHAR_LENGTH(PRACT_NUMBER) > 4
    -> AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
    -> AND Booking_ID = 6030
    -> GROUP BY PRACT_NUMBER;
    ERROR 1111 (HY000): Invalid use of group function
    mysql>

    Can anyone tell me why this is happening?

    Thanks for your advice
    --
    Stefan Kuhn M. A.
    Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de)
    Zülpicher Str. 47, 50674 Cologne
    Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786
    My public PGP key is available at http://pgp.mit.edu

Related Discussions

Discussion Navigation
viewthread | post