By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE products (
`id` INTEGER,
`product` VARCHAR(5),
`more fields` VARCHAR(9)
);
INSERT INTO products
(`id`, `product`, `more fields`)
VALUES
('1', 'hat', 'feathered'),
('2', 'pants', 'jeans'),
('3', 'pants', 'cargo');
CREATE TABLE product_ratings (
`id` INTEGER,
`product_id` INTEGER,
`rating` INTEGER
);
INSERT INTO product_ratings
(`id`, `product_id`, `rating`)
VALUES
('1', '1', '5'),
('2', '2', '2'),
('3', '3', '4'),
('4', '1', '4'),
('5', '3', '3');
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;
| id | product | more fields |
|---|---|---|
| 1 | hat | feathered |
| 3 | pants | cargo |
| 2 | pants | jeans |
SELECT p.*
FROM products p
ORDER BY (SELECT AVG(rating) AS avg_rating FROM product_ratings WHERE product_id = p.id) DESC;
| id | product | more fields |
|---|---|---|
| 1 | hat | feathered |
| 3 | pants | cargo |
| 2 | pants | jeans |