php/mySQL: Can I use COUNT in a WHERE statement?

[dwardio]

I need to generate a page in php that will report scores from a contest. Specifically, I need to display a list of participants that have made 3 or more entries, as well as their average score.

So far my SELECT stmt looks like:

SELECT user_name,
AVG(score) AS avg_score,
COUNT(score) AS entries
FROM scores
GROUP BY user_name
ORDER BY entries DESC, avg_score

This does everything I need except limit the query to those folks who have entered 3 or more times (it of course sorts it by number of entries instead).

I get an error when I try adding:
WHERE entries >= 3

Any ideas?

Thanks, Dennis

[Sxooter]

close, so close. 😉 Try using the "having" modifier.

select count(field1) from table group by field1 having count(field1)>3;

Note that in most databases you need to explicitly use an aggregate, since the alias for the counted field doesn't exist when the parser gets to the having part. I.e. the count(field1) actually gets computed AFTER the having count(field)>3 on most databases.

[dwardio]

That worked perfectly-- thanks!