title: Calculate new Average by using old AverageRating and New Value
body: <p>I am implementing a rating system in asp.net. I have a table for Rating. The structure of Rating table is simple,</p>
  
  <p>Table Name: Rating</p>
  
  <pre><code>Id      ProductId      RatingValue
  1          1                3.5
  2          1                3.0
  3          1                2.5
  4          2                4.0
  </code></pre>
  
  <p>Now while selecting data from sql table I use built in AVG function,</p>
  
  <pre><code>ISNULL(AVG([Rating].[RatingValue]), 0)
  </code></pre>
  
  <p>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.</p>
  
  <p>I now have two options.</p>
  
  <p>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)</p>
  
  <p>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.</p>
  
  <p>Which option is best and how to achieve them?</p>