Average and count for each SQL product

Question

I’m trying to update a table in my database into two new columns, one that brings the average of the ratings and one that brings the number of ratings.

These data are obtained through two tables, the relationship between them is the id and the product_id.

I tried the following code:

UPDATE product 
INNER JOIN 
 product_review ON product.id = product_review.product_id
SET 
 product.review_rating = (
  SELECT AVG(product_review.rating) WHERE state='approved'
 );

But it returns the average of all products and not of each one separately, some suggestion?

Answer 1

Try a test by doing a Join with a derived table where the average is calculated. Ex:

UPDATE product 
INNER JOIN 
(
  SELECT product_id, AVG(product_review.rating) as review_rating
  FROM product_review
  WHERE state='approved'
  GROUP BY product_id
) rv
  on product.id = rv.product_id 
SET product.review_rating = rv.review_rating

In this derived table you can also count the evaluations you mentioned.