SQL filtering by Average

0
votes

i have two tables products and reviews
each product has several reviews linked by a foreign-key product_id in the reviews table
additionally each review has a field called rating with a decimal value
i wish to get the names of all products whose average rating is above a certain threshold
something in the lines of 

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

MySQL is not letting me use the avg function in the where clause.
How do I do something like this?

1
sql

1 Answers

7
votes

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