calculate avg from one table and join to another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

calculate avg from one table and join to another

Post 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 :)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Post 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 :)
Post Reply