Slow SELECT statement, possibly due to WHERE?

Question

Database has a few million rows.

This is meant to find products priced above the average price:

SELECT ProductName, Price 
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

However, it's pretty slow.

I think this is because it recalculates the average for every row.

Is there a way to optimize this query?

I tried a few different types of queries, though I think I'm missing something.

Answer 1

You can use HAVING:

SELECT ProductName, MAX(Price) AS Price
FROM Products 
GROUP BY ProductName
HAVING (SELECT AVG(Price) FROM Products) < Price;