Search code examples
sqlmysqlrating

MySQL Rating System - Find Rating

I'm trying to rate Types of Fruit by the best possible rating given by users.

I have a Fruit table called Ratings just for submitting ratings with the following Information

Fruit_id int
From_ID int
Rating int

now I'm trying to run a SQL command like the following

select From_ID, AVG(Rating) AS Rating FROM Ratings Group BY `Fruit_ID` ORDER by Rating DESC

This works however, if i get 1 user who rates a fruit an apple a 5, and then 1000 users who rate an orange a 4 the apple is suddenly considered better.

How can i take number of votes into account, to ensure that the most voted along with best is first.

Solution

  • Why not just select the number of votes and display that to the user. So yes, 1 vote of 5 for an apple would be an overall 5 BUT with only 1 vote. This would give the users a simple way to see how "popular" a given rating is.

    This is how most of the fivestar rating systems work on the net today if I am not mistaken.

    Good luck!

    Nick