ORDER BY AVG and COUNT

[iceblox]

Hi All,

 

I have a table of reviews and im trying to return the top 4 rated products by doing the below;

 

SELECT AVG(rating) as average_rating, COUNT(id) as number_reviews FROM ratings GROUP BY id ORDER BY average_rating DESC, number_reviews DESC 

 

The problem I have is that the average rating is not ordered correctly it seems to be giving priority to the number_reviews as this is ordered correctly.

 

Ive tried adding

WHERE number_reviews > 3 

but this doesnt seem to work.

 

Does any one have any suggestions on how best to return the data I need?

 

Many Thanks,

 

[fenway]

You mean HAVING, not WHERE -- you can't use aliases in the WHERE clause, they don't exist yet.

[iceblox]

Thank you fenway! Wasnt aware of HAVING, thanks that does the trick ill look into this function some more.

 

Thanks again