SQL compare aggregated averages (Northwind)

Question

My question refers particularly to the Northwind database, therefore I don't provide any reproducible example/data set.

I'd like to select all Products whose UnitPrice is greater than the average UnitPrice for each category, excluding the one Category that the given Product belongs to. I've tried two different approaches, none of which yielded desired results so far.

This one returns an aggregated data set, but I don't know how to (I guess using having by?) compare each UnitPrice with mean unit price for all categories except the one that given Product belongs to

select 
p.ProductName, 
UnitPrice, t.mean, t.CategoryID from Products as p
inner join
(select avg(UnitPrice) as mean, CategoryID from Products
group by CategoryID) as t
on p.CategoryID = t.CategoryID

Here I was able to compare UnitPrice with the total average of all categories, not excluding the one that the given Product belongs to

SELECT x.ProductName, AVG(x.UnitPrice) AS average
FROM Products x
GROUP BY x.CategoryID, x.ProductName
Having AVG(x.UnitPrice)> 
(select AVG(UnitPrice) from Products)

The desired results should look like

Thanks.

Answer 1

You could phrase this with a lateral join:

select p.*, a.avg_unitprice
from products p
cross apply (
    select avg(p1.unitprice) avg_unitprice
    from products p1
    where p1.categoryid <> p.categoryid
) a
where p.unitprice > a.avg_unitprice 

This compares the unit price of each product to the average unit price of all products in other categories.

If, on the other hand, you want products whose price is greater than the all averages of other categories, then not exists seems like a better fit:

select p.*
from products p
where not exists (
    select 1
    from products p1
    where p1.categoryid <> p.categoryid
    group by p1.categoryid
    having avg(p1.unitprice) >= p.unitprice
)