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: [email protected]
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]
