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

avatar 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

avatar image

graz ♦
525 2 6 10

(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

avatar 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

more ▼

answered Aug 14, 2010 at 03:47 AM

avatar 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.

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:

x2017
x49

asked: Feb 16, 2010 at 08:28 AM

Seen: 3462 times

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

Copyright 2016 Redgate Software. Privacy Policy