Have Geek Brain

SQL filtering by mean

I have two product and review tables
each product has multiple reviews linked by the foreign key product_id in the reviews table
Also, each review has a field called rating with a decimal value
I want to get the names of all products whose average rating exceeds a certain threshold. something in the lines

SELECT p.name
FROM products p
INNER JOIN reviews r ON p.id = r.product_id
WHERE avg(r.rating) > 3

MySQL does not allow me to use the avg function in the where clause.
How can I do something like this?

0


a source to share


1 answer


use 'having'



SELECT p.name, avg(r.rating) as average
FROM products p
INNER JOIN reviews r ON p.id = r.product_id
GROUP BY p.name
HAVING avg(r.rating) > 3
+7


a source






More articles:


All Articles