Star Rating

[Newsgroup_User]

I'm trying to create a star rating system.
I have so far managed to create the rating system for individual products.
What i now wnat to do is work out the average star rating for each product.
I have a racordset that gets the product ID and the rating number 1 to 5
How do i total the rating numbers and divide by the number of ratings given?

e.g.
The recordset has 10 results and the total of all of the ratings is 63

Thanks
Andy

[Newsgroup_User]

On 08 Nov 2007 in macromedia.dreamweaver.appdev, Andy wrote:

> I'm trying to create a star rating system.
> I have so far managed to create the rating system for individual
> products. What i now wnat to do is work out the average star rating
> for each product. I have a racordset that gets the product ID and
> the rating number 1 to 5 How do i total the rating numbers and
> divide by the number of ratings given?
>
> e.g.
> The recordset has 10 results and the total of all of the ratings is
> 63

SQL for MySQL; may differ for other rDBMSes:

SELECT `productID`, SUM(`rating`), COUNT(`productID`)
FROM `products`
GROUP BY `productID`
ORDER BY `productID`

Note the GROUP BY clause - it will group all items with the same
product ID. The ORDER BY is not necessary; if you only want a single
product, you can replace it with a WHERE `productID` = [something]

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

[Newsgroup_User]

Joe Makowiec wrote:
> On 08 Nov 2007 in macromedia.dreamweaver.appdev, Andy wrote:
>
>> I'm trying to create a star rating system.
>> I have so far managed to create the rating system for individual
>> products. What i now wnat to do is work out the average star rating
>> for each product. I have a racordset that gets the product ID and
>> the rating number 1 to 5 How do i total the rating numbers and
>> divide by the number of ratings given?
>>
>> e.g.
>> The recordset has 10 results and the total of all of the ratings is
>> 63
>
> SQL for MySQL; may differ for other rDBMSes:
>
> SELECT `productID`, SUM(`rating`), COUNT(`productID`)
> FROM `products`
> GROUP BY `productID`
> ORDER BY `productID`
>
> Note the GROUP BY clause - it will group all items with the same
> product ID. The ORDER BY is not necessary; if you only want a single
> product, you can replace it with a WHERE `productID` = [something]
>

Couldn't you also use the AVG SQL function?

SELECT productID, AVG(rating) AS Average, SUM(rating) AS Total,
COUNT(productID) AS Count
FROM products
GROUP BY productID
ORDER BY productID