We are facing problem in calculating average of votes or stars for a particular restaurant name or id
We have two tables named Restaurant and Comments Restaurants has foolowing columns RestId,RestName,Rating,.......... Comments has RestId,RestName,Rating,Average.........
In comments table we store comments with rating(1,2,3,4,5) in Rating column and Average is to be calculated and stored in Average column This Average column value is then updated in Reataurant table in Rating column Actually we are trying to calculate average using "select avg(rating) from comments where RestName=''" and directly storing value into Restaurant table without using average column
then also tried
but still we are not getting the average per restaurant
We want a procedure to calculate average of rating from comments table and storing the value into restaurant table
EDIT -- Additional information
The result which i got is:- In the comments table individual rating is stored but taking avg(rating) from comments table it has to store the value into restaurants table for each restaurant but same avg(rating) is stored in each restaurant
This is the result but its taking round() 3.50000 but it has to be 3.5000 and 5.0000
so please help
The situation is like
I have Restaurant table in which only one record for all the retaurants entry is stored then there is comments table in which each restaurant vl have ratings from 1....5 so from this table i have to calculate avg(rating) per restaurant and update the Restaurant table wit the avg(rating) value
Ultimately there vl b average for each restaurant eg:there vl be many entries in Comments Table for Mcdonald,Pizza Hut ...etc but i wanna calculate average of all entries from Comments Table for Mcdonald and store in Restaurant table for Mcdonald ......similarly calculating average of all entries from Comments Table for Pizza Hut and store in Restaurant table for Pizza Hut
answered Feb 22 '10 at 03:52 AM
This will convert an integer to a decimal value prior to averaging.
answered Feb 18 '10 at 04:58 PM