mysql - 需要帮助的初学者 sql 学生

Question

我对 sql 和 Stack Overflow 非常陌生。我希望有人可以帮助我解决这个问题。我的查询应该显示销售额超过 200,000 美元的类别的总销售额和已售商品总数。我已经研究这个查询一个小时了，我已经束手无策了，非常感谢您的帮助!

   select distinct c.categoryname,
   sum(p.unitprice * od.Quantity) as 'Sales'
   from Categories c
   inner join Products p
   on c.CategoryID = p.CategoryID
   inner join OrderDetails od
   on p.ProductID = od.ProductID
   where p.unitprice < 200000 
   group by c.categoryname

我希望我至少走在正确的道路上，感谢您的帮助!

Answer 1

尝试这个版本:

SELECT
   c.categoryname,
   sum(od.Quantity) as "Items Sold",  -- you were missing this
   sum(p.unitprice * od.Quantity) as "Sales"
FROM
   Categories c
   INNER JOIN Product p ON c.CategoryID = p.CategoryID,
   INNER JOIN OrderDetails od on p.ProductID = od.ProductID
WHERE
   sum(p.unitprice * od.Quantity) > 200000  -- filter on the sales, not product
GROUP BY
   c.categoryname