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

2
votes

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-509LB9L\MSSQLSERVER01].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-509LB9L\MSSQLSERVER01].northwind.dbo.products p
group by p.UnitPrice
having p.UnitPrice > AVG(p.UnitPrice)
2
sql
Show us some sample table data and the expected result - all as formatted text, not images. (minimal reproducible example) - jarlh

2 Answers

1
votes

Use a subquery instead:

select p.ProductName, p.UnitPrice
from [DESKTOP-509LB9L\MSSQLSERVER01].northwind.dbo.products p
where p.UnitPrice > (select avg(p2.UnitPrice)
                     from [DESKTOP-509LB9L\MSSQLSERVER01].northwind.dbo.products p2
                    );
1
votes

You need to use a subquery to compute average before compare it to unit price

select p.ProductName, p.UnitPrice
from northwind.dbo.products p
where p.UnitPrice>
(
    select AVG(p2.UnitPrice)
    from northwind.dbo.products p2
    group by p2.ProductName
    having p2.ProductName = p.ProductName
)

You should ensure your product name is unique or use the product key instead of name