SQL Query aggregate may not appear in WHERE clause (AVG)

Hi I can’t find a solution how to fix this problem.

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

select p.ProductName, p.UnitPrice
from [DESKTOP-509LB9LMSSQLSERVER01].northwind.dbo.products p
where p.UnitPrice > AVG(p.UnitPrice)
group by p.UnitPrice

Should be like this : AVG is 28.3 so UnitPrice higher than 28.3

ProductName                        UnitPrice
Uncle Bob's Organic Dried Pears     30,00
Northwoods Cranberry Sauce          40,00
Mishi Kobe Niku                     97,00
Ikura                               31,00
Queso Manchego La Pastora           38,00
Alice Mutton                        39,00

Having also don’t work

select p.ProductName, p.UnitPrice
from [DESKTOP-509LB9LMSSQLSERVER01].northwind.dbo.products p
group by p.UnitPrice
having p.UnitPrice > AVG(p.UnitPrice)

Answer

Use a subquery instead:

select p.ProductName, p.UnitPrice
from [DESKTOP-509LB9LMSSQLSERVER01].northwind.dbo.products p
where p.UnitPrice > (select avg(p2.UnitPrice)
                     from [DESKTOP-509LB9LMSSQLSERVER01].northwind.dbo.products p2
                    );