Page 1 of 1

calculate avg from one table and join to another

Posted: Tue Mar 13, 2007 4:33 pm
by ed209
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 :)

Posted: Tue Mar 13, 2007 4:54 pm
by volka
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

Posted: Wed Mar 14, 2007 2:52 am
by ed209
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 :)