Comparing group-by result with aggregated result

I am trying to retrieve details about stores where their average product prices are higher than the average of all stores in the table.

So far, I have this code:

select StoreID, count(ProductID) from Products
where (select avg(Price) from Products group by StoreID) > (select avg(Price) from Products)
group by StoreID;

But this returns the following error:

single-row subquery returns more than one row

In terms of data, I would like to get this result:

StoreID  count(ProductID)
-------  ----------------
1        2
2        4

where the average prices of those stores are:

StoreID  avg(Price)
-------  ----------
1        6.5
2        7.5

and the average price of all stores is 4.6.

Can you help? Thanks in advance.

Answer

You are trying to express:

select p.StoreID, count(*)
from Products
group by p.StoreId
having avg(Price) > (select avg(Price) from Products);

That is, you want a having clause and don’t need to re-aggregate the data at the StoreId level.

Advertisement

Answer