How to include count(*) in selection criteria
Dear all, I am trying the following mysql statement (probably it will be obvious that I am a newcomer): select IMdirectory, IMljd, count(*) as N from Images where IMstid = 5, N 10 group by IMdirectory order by IMljd; ERROR 1054: Unknown column 'N' in 'where clause' My question is: how could I select only those grouped entries that have a count number greater than e.g. 10? Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to include count(*) in selection criteria
At 12:52 -0500 12/17/03, Gaspar Bakos wrote: Dear all, I am trying the following mysql statement (probably it will be obvious that I am a newcomer): select IMdirectory, IMljd, count(*) as N from Images where IMstid = 5, N 10 group by IMdirectory order by IMljd; ERROR 1054: Unknown column 'N' in 'where clause' My question is: how could I select only those grouped entries that have a count number greater than e.g. 10? You cannot refer to aggregate values in a WHERE clause, because: - WHERE determines which rows to select - aggregates are calculated from the rows that are selected In other words, you cannot use values that are determined from the selected rows to determine which rows to select. :-) Try putting the COUNT() test in a HAVING clause instead. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to include count(*) in selection criteria
use having N 10 The having clause takes the results of the query AFTER all of the rows have been read and aggregated by the group by clause and further reduces the set of rows that gets returned. - original message - Date: Wed, 17 Dec 2003 12:52:08 -0500 (EST) From: Gaspar Bakos [EMAIL PROTECTED] To: mysqllist [EMAIL PROTECTED] Subject: How to include count(*) in selection criteria Dear all, I am trying the following mysql statement (probably it will be obvious that I am a newcomer): select IMdirectory, IMljd, count(*) as N from Images where IMstid = 5, N 10 group by IMdirectory order by IMljd; ERROR 1054: Unknown column 'N' in 'where clause' My question is: how could I select only those grouped entries that have a count number greater than e.g. 10? Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to include count(*) in selection criteria
Hi, RE: In other words, you cannot use values that are determined from the selected rows to determine which rows to select. :-) Sounds very logical. In fact I was not surprised that my query did not work, I just had no idea about the workaround. Thanks again! Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
