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]
