x

calculating average of star rating system using .net and sql server 2005

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

more ▼

asked Feb 16, 2010 at 08:28 AM in Default

suja gravatar image

suja
4 2 2 2

What result did you get? What result did you expect?
Feb 16, 2010 at 09:51 AM graz ♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Try:

select avg(CAST(rating AS DECIMAL(6,5)) as average             
from comments             
where RestName='Mcdonald'            

This will convert an integer to a decimal value prior to averaging.

more ▼

answered Feb 18, 2010 at 04:58 PM

graz gravatar image

graz ♦
525 2 4 5

@suja
Do you want to calculate the average over ALL McDonald's restaurants, or the average for each individual store?
Feb 18, 2010 at 11:20 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

hi graz

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

Pls help
suja

more ▼

answered Feb 22, 2010 at 03:52 AM

suja gravatar image

suja
4 2 2 2

(comments are locked)
10|1200 characters needed characters left

The savings account under your mattress doesn't count. If you're like a lot of other people, you think of a savings account ..

[Savings Accounts][1]

[1]: http://heathsavingsaccounts.com
more ▼

answered Aug 14, 2010 at 03:47 AM

paulhar333 gravatar image

paulhar333
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x46

asked: Feb 16, 2010 at 08:28 AM

Seen: 2219 times

Last Updated: Feb 18, 2010 at 04:56 PM