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:
x
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.
Advertisement
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.