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


-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]



Reply via email to