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
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[average](@RestId varchar(50)) as update restaurants set rating=(select avg(rating) from comments where RestName=@RestId) exec average '1' select * from comments select * from restaurants select avg(rating) from comments where RestName='Mcdonald'
then also tried
create proc rate_calculate(@RestName varchar(50)) as begin select res.RestName,res.rating from restaurants as res left join(select RestName,avg(rating) as average from Comments where RestName=@RestName) as c on c.RestName=res.RestName go
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
> select * from comments 42 excellent1 email@example.com test NULL 2010-02-16 13:50:14.000 5 Mcdonald ---------- 43 excellent 2 suja.p03@gmail test NULL 2010-02-16 14:31:54.000 5 Pizza Hut ---------- 44 excellent 1 firstname.lastname@example.org test NULL 2010-02-16 14:51:19.000 5 Mcdonald ---------- 45 good 1 email@example.com test NULL 2010-02-16 15:08:32.000 3 Mcdonald ---------- 46 good 1 firstname.lastname@example.org test NULL 2010-02-16 15:16:18.000 1 Mcdonald ---------- 47 good 2 email@example.com test NULL 2010-02-16 15:22:08.000 5 Pizza Hut** ---------- > select avg(rating) as average from comments where RestName='Mcdonald' average 3.500000 > select avg(rating) as average from comments where RestName='Pizza Hut' average 5.000000
This is the result but its taking round() 3.50000 but it has to be 3.5000 and 5.0000
> select * from restaurants RestName Rating Mcdonald 4 Pizza Hut 4
so please help