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]
