CODEWITHSUNDEEP
sql
Simonsoft177
Simonsoft177

Reputation: 175

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-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)

Upvotes: 2

Views: 270

Answers (2)

Troopers
Troopers

Reputation: 5452

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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
                    );

Upvotes: 1

Related Questions