inner join returns only 1 row
let me sketch the situation a bit: i have 2 tables: producten and reviews, in producten i have multiple products stored, and in reviews i have stored all the reviews now while attemption to get the average of the ratings in the reviews table mysql only returns 1 row while i expect 2 rows back since i have 2 products.
the query im trying to use is:
SELECT p.*, CAST(AVG(r.rating) AS DECIMAL(2,1)) as waardering FROM `producten` as p INNER JOIN `reviews` as r ON p.id=r.product_id
i have also tried:
SELECT p.*, CAST(AVG(r.rating) AS DECIMAL(2,1)) as waardering FROM `producten` as p INNER JOIN `reviews` as r ON r.product_id=p.id
but this diddnt returned more then 1 row either.
could anyone please tell me why the query is only returning 1 row and not all the rows it finds inside the producten table?
also please feel free to tell me how i could increase this question if needed.
You want to GROUP BY p.id at the end of your query so that you get the average per product, instead of the values in the "first" product's field with the average for all products.
It's because you are using AVG. It is going to group all the rows together as one row. You probably want to group by avg rating instead.
So if I had a table with two columns.. product name / rating... I would do like so:
select productname, avg(rating) from ratings group by productname