mysql question related to where + count

Forum Moderators: open

Message Too Old, No Replies

mysql question related to where + count

oxidetones

7:30 pm on Feb 17, 2008 (gmt 0)

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

5:25 am on Feb 18, 2008 (gmt 0)

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

8:18 pm on Feb 18, 2008 (gmt 0)

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

ZydoSEO

8:53 pm on Feb 18, 2008 (gmt 0)

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

10:23 pm on Feb 18, 2008 (gmt 0)

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

1:47 pm on Feb 19, 2008 (gmt 0)

cool stuff, thanks!