That did it, thank you all! -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 2:10 AM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: WHERE problem, or is it a problem?
Peter Lauri wrote: > Best group member, > > I have this query on MySQL version 4.0.27: > > SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans > FROM tblparticipants part > LEFT JOIN tblanswers answer ON (answer.par_num=part.memberid) > LEFT OUTER JOIN profilepdfauth pdfauth ON (part.memberid=pdfauth.memberid) > WHERE pdfauth.id IS NULL > GROUP BY part.memberid > ORDER BY numberofans DESC, part.memberid; > > This works fine, > > However, I only want the results where COUNT(*)>=31. So I tried: > > SELECT part.memberid, part.prefname, part.email, COUNT( * ) AS numberofans > FROM tblparticipants part > LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) > LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = > pdfauth.memberid) > WHERE pdfauth.id IS NULL > AND COUNT( * ) >=31 > GROUP BY part.memberid > ORDER BY numberofans DESC , part.memberid > > But then MySQL answered with: #1111 - Invalid use of group function > > What is the problem here? Why can I not do a WHERE COUNT(*)>=31? > > Is there any other way to just select the COUNT(*)>=31? > > Best regards, > Peter Lauri WHERE conditions determine which rows to select. You can't count how many rows you've selected until after you've selected them. Use HAVING to filter the results after selection. Try: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid HAVING numberofans >=31 ORDER BY numberofans DESC , part.memberid Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]