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 sajeed@gmail.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 sajeed@gmail.com test NULL 2010-02-16 14:51:19.000 5 Mcdonald
----------
45 good 1 sajeed@gmail.com test NULL 2010-02-16 15:08:32.000 3 Mcdonald
----------
46 good 1 sajeed@gmail.com test NULL 2010-02-16 15:16:18.000 1 Mcdonald
----------
47 good 2 sajeed@gmail.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
