1-5 Star rating rank - How to calculate an average score

Question

I have 4 sets of a star rating widget that saves to my db, values (1-5) from each star, where users can rate other users using those 4 star groups.

Something like:

is user x a jealous person? * * // (=2)
is user x a happy person? * * * * // (=4) 
is user x a clever person? * * * * * // (=5)
is user x a humble person? * * * * * // (=5)

So after a vote I get a record like this:

*mysql sample row data

-------------------------------------------------------------------
ID  from_user_id  to_user_id rate_value
-------------------------------------------------------------------
1    2234         123         2    
2    2234         123         4   
3    2234         123         5   
4    2234         123         5   
-------------------------------------------------------------------

Running some sort a query. results the folllowing

    id=1
    from_user_id = 2234
    to_user_id = 123
    a1=2
    a2=4
    a3=5
    a4=5

So best score a user can have is 20.

After an user have two or more score rows, I would like to save to another table a decimal score for him, from 0 to 10, say like: 7.1.

How can I do that? Sorry, I can't math.

Answer 1

If you want a 1-10 scale,

all you have to do is divide their total score by 2. So if they have 15 out of 20, that is equivalent to having 7.5 out of 10. >because 15/20 = 0.75 as does 7.5/10.

Answer 2

Well, first we need to determine what you are basing your score of 10 on. If we are basing this off of the stars, they only go up to 5, so we'll double it.

//Initialize Variable
$score = 0;

//Use for loop to go through $row['a#'] values
for( $x = 1; $x < 5; ++$x )
{
    //Add $row['a#'] after multiplying
    $score += ( $row['a'.$x] * 2 );
}
//divide by 4 (number of scores) and round to first decimal spot
$score = round( $score/4, 1 );

echo $score; //Outputs 8  ( 4+8+10+10 = 32 => 32/4 = 8 )

Answer 3

Algorithm:   Calculate_Average_Star_Rating ( )
1.  for each (product exixts in table_product HDB) do
2.  for each (Authenticated user rate for respective product in table_rating HDB) do
3.      Read corresponding weight from ratings column in table_rating HDB
        Calculate Weighted_Mean = ∑ ( weight * number of users at that weight )/
                                             Total number of users
4.  end for 
5.  Store Weighted_Mean in corresponding rows avg_rating column in table_product HDB
6. end for