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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
