mysql question related to where + count

[oxidetones]

I have a rating system on my site, and a page to show the results. The query I use for the results page is as follows:

SELECT rating_id, title_art, id_top, description_art, title_top, AVG(rating_num) as rating
FROM ratings INNER JOIN dbReleases ON id_art = rating_id INNER JOIN dbBands on id_top = idtop_art
GROUP BY rating_id
ORDER BY rating DESC LIMIT 50

What I want to do is only show the averages where at least 3 votes have been made, otherwise the results page is weird;

I thought I could do WHERE count(id_top)>3 byt that doesnt work. Can some=one point me in the correct direction?

Thanks

[ZydoSEO]

Did you try something like:

SELECT rating_id, title_art, id_top, description_art, title_top, AVG(rating_num) as rating, COUNT(*)
FROM ratings INNER JOIN dbReleases ON id_art = rating_id INNER JOIN dbBands on id_top = idtop_art
GROUP BY rating_id
HAVING COUNT(*) > 3
ORDER BY rating DESC LIMIT 50

[edited by: ZydoSEO at 5:27 am (utc) on Feb. 18, 2008]

[oxidetones]

that seems to work, thanks, although Im not sure I understand why...

[ZydoSEO]

Read up on group by having... It basically says only return the avg(rating_num) where there are more than 3 rows that were summed up to calculate the average.

You were on the right track. It just can't be done as part of the WHERE clause... SQL has it's on HAVING clause to be used in conjunction with GROUP BY for such situations.

[edited by: ZydoSEO at 8:54 pm (utc) on Feb. 18, 2008]

[syber]

A good way to think about it is the WHERE clause is used to restrict the number of rows to be grouped, while the HAVING clause restricts the number of groups.

[oxidetones]

cool stuff, thanks!