Star Rating

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

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