我试图回答哪个产品ID和ProductName带来的收入比商店中产品的平均收入更多的问题。
我有以下代码:
Select ProductName, SupplierName, MAX(Quantity*ProductPrice) as TotalRev
From Products
Where MAX(TotalRev) > AVG(TotalRev)
然而,这会导致错误。
示例数据
ProductID ProductName SupplierName ProductType Quantity ProductPrice
10001 GreenJacket GAP Jackets 100 $10
10002 StarEarrings Oldnavy Accessories 200 $5
10003 YellowDress BRP Dress 150 $10
理想情况下,我希望代码吐出产品ID和产品名称,其中产品带来的收入超过平均收入。
你需要
一个having
子句和一个子查询:
Select ProductId, ProductName,
SUM(Quantity*ProductPrice) as TotalRev
From Products
group by ProductId, ProductName
having SUM(Quantity*ProductPrice) >= (select avg(revenue)
from (select sum(p2.quantity * p2.ProductPrice) as revenue
from products as p2
group by p2.ProductId, p2.ProductName
) as p3
);
我建议您在 having
子句中运行子查询,以便您完全了解它在做什么。