SQL LEFT JOIN, Average and WHERE

Question

So I have been trying this for over a week and a bit, I put it to rest because I couldn't get it to work but this is what I am trying to do;

Select a product from the database > Use LEFT OUTER JOIN to load its rating and make an average of it > Use INNER JOINs for different information (not really important, but I will say it anyway).

This is a simplified (but accurate) replica of my query - the original is quite long and has different language words in it so it might get a bit confusing;

SELECT 
PRODUCT.name AS Name,
PRODUCT.price AS Price,
BRAND.name AS Brand,
AVG(RATING.rating) AS Rating
FROM PRODUCT
LEFT OUTER JOIN RATING ON RATING.product_id = PRODUCT.product_id
INNER JOIN BRAND ON BRAND.brand_id = PRODUCT.brand_id
GROUP BY PRODUCT.name, PRODUCT.price, BRAND.name

The above works, but it misses the 'filtering' on rating. I wish adding WHERE AVG(RATING.rating) > 3 would work, but sadly it doesn't. Is this in any way possible to do?

Thanks.

Answer 1

Try HAVING AVG(RATING.rating) > 3 after your GROUP BY.

See HAVING on MSDN for more details.

Answer 2

You should use the HAVING clause. The WHERE clause filters the result set prior to grouping, whereas the HAVING clause filters the final result set, after grouping and aggregation. So in WHERE clause you can't use the aggregates (AVG(RATING.rating) in this case), whereas in the HAVING, you can do so.

SELECT 
PRODUCT.name AS Name,
PRODUCT.price AS Price,
BRAND.name AS Brand,
AVG(RATING.rating) AS Rating
FROM PRODUCT
LEFT OUTER JOIN RATING ON RATING.product_id = PRODUCT.product_id
INNER JOIN BRAND ON BRAND.brand_id = PRODUCT.brand_id
GROUP BY PRODUCT.name, PRODUCT.price, BRAND.name
HAVING AVG(RATING.rating) > 3