|| 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.
Am Wednesday 11 May 2005 20:36 schrieb shaun thornburgh:
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
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