Re: ERROR 1111 (HY000): Invalid use of group function
Paul DuBois wrote: At 18:36 + 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 (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]
Re: ERROR 1111 (HY000): Invalid use of group function
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 (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]
Re: ERROR 1111 (HY000): Invalid use of group function
At 18:36 + 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 (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. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1111 (HY000): Invalid use of group function
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 (HY000): Invalid use of group function mysql> Can anyone tell me why this is happening? Thanks for your advice -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
