calculate avg from one table and join to another
Posted: 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
Table 2 Ratings
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.
Result I'd like to get returns both products in the product table. Result I actually get is just product 1.
Thanks in advance
Table 1 Products
Code: Select all
----------------------------
product_id | product_details
----------------------------
1 | toaster
2 | microwaveTable 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 | 5How 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 DESCResult I'd like to get returns both products in the product table. Result I actually get is just product 1.
Thanks in advance