CODEWITHSUNDEEP
asp.netsql-server-2008c#-4.0
Adnan Yaseen
Adnan Yaseen

Reputation: 853

Calculate new Average by using old AverageRating and New Value

I am implementing a rating system in asp.net. I have a table for Rating. The structure of Rating table is simple,

Table Name: Rating

Id      ProductId      RatingValue
1          1                3.5
2          1                3.0
3          1                2.5
4          2                4.0

Now while selecting data from sql table I use built in AVG function,

ISNULL(AVG([Rating].[RatingValue]), 0)

This work fine. Now when the user rate the same product again in the database then I have to insert the new rating in the Sql table "Rating" and have to update the label control with the new average rating. I can do this by using a select statement after saving the record in the database table but this will an extra round trip to the database. Now I have Old AverageRating value and the number of times the product is rated.

I now have two options.

Option 1: Either somehow return the new AverageValue from within the Insert Statement (I could select identity column but don't know whether I could select Average of column or not)

Option 2: Use the old AverageRating and CountRated values in application. I can use the new value of rating with these values to calculate the new value at runtime.

Which option is best and how to achieve them?

Upvotes: 0

Views: 583

Answers (1)

Xaruth
Xaruth

Reputation: 4104

If CountRated is the number of times the product is rated, before the new value of RatingValue :

AverageRating = (AverageRating * CountRated + RatingValue) / (CountRated + 1);

It can be faster than recalculating average from all data, but it may be less accurate with lot of data.

Upvotes: 3

Related Questions