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: # - 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]
Re: WHERE problem, or is it a problem?
Use the HAVING clause: 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 count(*) =31 ORDER BY numberofans DESC, part.memberid; Peter Lauri [EMAIL PROTECTED] 07/26/2006 09:58 AM To mysql@lists.mysql.com cc Subject WHERE problem, or is it a problem? 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: # - 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
RE: WHERE problem, or is it a problem?
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: # - 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]