MySQL Rating System - Find Rating

Question

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.

Answer 1

This question is more about tweaking your mathematical formula than anything else.

It sounds like what you are asking for is popularity, rather than rating. A simple way to do this might be to multiply the average rating by the number of votes. What about this (please excuse me if my syntax is off, I'm away from a SQL server at the moment):

select From_ID, AVG(Rating) * COUNT(Rating) AS Rating FROM Ratings Group BYFruit_IDORDER by Rating DESC

Keep in mind that this formula would rate 1000 votes of 1 much higher than 1 vote of 5.

Answer 2

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