List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:May 11 2005 6:50pm
Subject:Re: ERROR 1111 (HY000): Invalid use of group function
View as plain text  
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

Thread
ERROR 1111 (HY000): Invalid use of group functionshaun thornburgh11 May
  • Re: ERROR 1111 (HY000): Invalid use of group functionPaul DuBois11 May
    • Re: ERROR 1111 (HY000): Invalid use of group functionMichael Stassen11 May
  • Re: ERROR 1111 (HY000): Invalid use of group functionStefan Kuhn11 May