SQL - I'm trying to find the average of all fields that meet a condition and I'm getting: invalid in the HAVING clause

Question

SQL - I'm trying to find the average of all fields that meet a condition and I'm getting: invalid in the HAVING clause... Any help would be awesome.

use Pizza05
Select AVG(PizzaToppings.ToppingPrice + Products.ProductPrice) as Average
from OrderedProducts
Left Join PizzaToppings ON PizzaToppings.ToppingID = OrderedProducts.ToppingID
Inner Join Products ON Products.ProductID = OrderedProducts.ProductID
having OrderedProducts.QuantityOrdered > 10

Answer 1

You have to replace having with where, since having is used to specify conditions on aggregate functions, not single columns. See here.

Answer 2

You should use HAVING to filter over grouped values, use standard WHERE to filter non-grouped fields.

Eg.

SELECT 
   AVG(T.ToppingPrice + P.ProductPrice) Average
FROM OrderedProducts O
LEFT JOIN PizzaToppings T 
   ON T.ToppingID = O.ToppingID
INNER JOIN Products P 
   ON P.ProductID = O.ProductID
WHERE 
   O.QuantityOrdered > 10