How to sort products by average rating from another table?

I’m new to databases, so I haven’t wrapped my head around how to use joins, subqueries etc. properly. That’s why I’m very unsure how to go about sorting products by the average of another table.

Here’s my usecase:

I have a straightforward products table in this style

|id|product|more fields|
|1 |hat    |feathered  |
|2 |pants  |jeans      |
|3 |pants  |cargo      |

And in a second table, produtcs_rating, I have the five-star ratings like

|id|product_id|rating|
|1 |    1     |   5  |
|2 |    2     |   2  |
|3 |    3     |   4  |
|4 |    1     |   4  |
|5 |    3     |   3  |

Now I want to be able to sort these on the website according to the average rating (simple arithmetic mean is fine). Would something like this be working?

SELECT * FROM products ORDER BY (SELECT AVG(rating) AS avg_rating FROM product_ratings) DESC;

Or is this something where I should use a join? And if, how? Any help for a beginner would be much appreciated!

Answer

Join products to a query that returns the average ratings:

SELECT p.* 
FROM products p 
INNER JOIN (
  SELECT product_id, AVG(rating) avg_rating
  FROM product_ratings 
  GROUP BY product_id
) r ON r.product_id = p.id
ORDER BY r.avg_rating DESC;

Or use a correlated subquery in the ORDER BY clause:

SELECT p.* 
FROM products p 
ORDER BY (SELECT AVG(rating) AS avg_rating FROM product_ratings WHERE product_id = p.id) DESC;

See the demo.
Results:

> id | product | more fields
> -: | :------ | :----------
>  1 | hat     | feathered  
>  3 | pants   | cargo      
>  2 | pants   | jeans