Hi all - i'm trying to put together my first .Net web page (have switched from Dreamweaver to VWD - VWD keeps swapping my tab-indents for spaces, and none of the options stop it!).
Here's a table that i'm trying to query: ItemID | ReviewRating | ReviewRatingOutOf
As i'm sure you've guessed, it's a reviews table, where there can be several records with the same ItemID and different (or the same) ReviewRating and ReviewRatingOutOf's. As the reviews are collected from lots of sources, the ReviewRatingOutOf will change (one review might be 3/5, while the next, for the same ItemID, could be 8/10, etc). Now, what i'm trying to do is return a list of ItemID's ordered by their RATIO (which is the sum of each ItemID's ReviewRating's divided by the sum of each ItemID's ReviewRatingsOutOf's - in other words, average score). My first guess was this:
"SELECT DISTINCT ItemID FROM Reviews ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" - unfortunately that doesn't work (problems with the SUM aggregate functions, and overflow errors, whatever they are). Now, this string works: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)" - right now, that just adds up the ReviewRatings, so an item with 10 reviews that only got awarded 1/5, 1/10, 1/8, etc (all 1's, therefore achieving a combined ReviewRating of 10 out of a very much higher ReviewRatingOutOf), would appear higher than an item with 1 review that got 5/5. Making the string into this: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" (which is what I need), unfortunately gives me errors...
Anyone have any ideas? Is there possibly a way to simply read all the distinct ItemID's with SQL, then get the two SUM's for each ItemID, then calculate the ratio of the two SUM's, and stick the ItemID's and the ratio into some sort of array, and have C# order the array for me, based on the ratio? I'd appreciate an example of that if possible, as i'm a complete C# beginner :-)
Thanks in advance!
anyone?|||SELECT ItemID
FROM Reviews
GROUP BY ItemID
ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)
or this:
SELECT ItemID
FROM Reviews
GROUP BY ItemID
ORDER BY AVG(ReviewRating/ReviewRatingOutOf)
If ReviewRating is an integer, and ReviewRatingOutOf is an integer, then you should cast one to a float like:
SELECT ItemID
FROM Reviews
GROUP BY ItemID
ORDER BY AVG(CAST(ReviewRating AS float)/ReviewRatingOutOf)
otherwise you won't get what you expect because (int) / (int) will always return the floor of the result. So 1/2=0 1/8=0, 1/10=0, and the only way to score a non-zero result would be a perfect 5/5, 10/10, etc. By casting one to a float, then it the divide will return a float result. So (float)1/(int)2=(float)0.5
|||Thanks for trying Motley - but all those methods give an 'overflow' error. I'm using Access 2003 by the way - just realised that may be important and that I hadn't mentioned it!
Any more help really appreciated!
|||Is there a record that ReviewRatingOutOf is 0?|||Ahhh - yeah there is. Is there an easy way around that, or is it best to simply remove/change it? It's a review that didn't have an accompanying score (obviously!).
Thanks again for you help so far Motley.
|||Think I got it working myself - here's my final code:
"SELECT ItemID FROM Reviews GROUP BY ItemID HAVING SUM(ReviewRatingOutOf) > 0 ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf) DESC"
That seems to give me exactly what I was after, with the highest-rated items arriving first. Unless you see something that'll cause problems, it's the perfect solution for me (that also allows for items with ReviewRatingOutOf = 0).
Thanks again for your help!
|||Just make sure that if both ReviewRating and ReviewRatingOutOf are both defined as an integer type (int,bigint,smallint,tinyint,bit) that you cast one or both to a float before the division or you'll get unexpected results.