4 messages in com.mysql.lists.mysqlRE: WHERE problem, or is it a problem?
FromSent OnAttachments
Peter Lauri26 Jul 2006 06:58 
Peter Lauri26 Jul 2006 07:37 
Michael Stassen26 Jul 2006 12:09 
ddev...@intellicare.com26 Jul 2006 12:11 
Subject:RE: WHERE problem, or is it a problem?
From:Peter Lauri (lis@dwsasia.com)
Date:07/26/2006 07:37:31 AM
List:com.mysql.lists.mysql

That did it, thank you all!

-----Original Message----- From: Michael Stassen [mailto:Mich@verizon.net] Sent: Thursday, July 27, 2006 2:10 AM To: Peter Lauri Cc: mys@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/mysql?unsub=lis@dwsasia.com