Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
ed209
Forum Contributor
Posts: 153 Joined: Thu May 12, 2005 5:06 am
Location: UK
Post
by ed209 » Tue Mar 13, 2007 4:33 pm
I have a table filled with products and another table that stores user submitted ratings of that product
Table 1 Products
Code: Select all
----------------------------
product_id | product_details
----------------------------
1 | toaster
2 | microwave
Table 2 Ratings
Code: Select all
------------------------------------------
rating_id | product_id | date | rating
------------------------------------------
1 | 1 | 12/12/06 | 5
2 | 1 | 12/11/06 | 3
3 | 1 | 12/11/06 | 2
4 | 1 | 12/10/06 | 5
5 | 1 | 12/10/06 | 4
6 | 1 | 12/10/06 | 5
How can I select all products and order them by average rating (with the best at the top and worst at the bottom)? The problem I am having is that not all products have been rated - and when I join they are not returned in the results.
Code: Select all
SELECT p.*,
SUM(r.rating) / COUNT(r.value) AS avg
FROM products p
JOIN rating r
ON p.product_id = r.product_id
GROUP BY r.product_id
ORDER BY avg DESC
Result I'd like to get returns both products in the product table. Result I actually get is just product 1.
Thanks in advance
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Tue Mar 13, 2007 4:54 pm
try
Code: Select all
SELECT
p.*,
Avg(r.rating) AS average
FROM
products p
LEFT JOIN
ratings r
ON
p.product_id = r.product_id
GROUP BY
r.product_id
ORDER BY
average DESC
ed209
Forum Contributor
Posts: 153 Joined: Thu May 12, 2005 5:06 am
Location: UK
Post
by ed209 » Wed Mar 14, 2007 2:52 am
Thanks for the reply, the AVG is a neat trick. It wasn't the fix but it led me to it. I was grouping by product_id but in the wrong table. Instead of r.product_id I should have grouped by p.product_id
Code: Select all
SELECT
p.*,
Avg(r.rating) AS average
FROM
products p
LEFT JOIN
ratings r
ON
p.product_id = r.product_id
GROUP BY
p.product_id
ORDER BY
average DESC
I'm still not sure I am getting the correct number of results through, I'll run a few tests without the JOIN and see what happens.
Thanks for your help