SQL:如何计算求和列的行数

Question

如何计算求和列的行数？

我已经建立了一个查询，显示每个订单中有多少项，但我需要对每个Sum进行计数。我的目标是看看有多少人买了3件商品，有多少人购买了4件，等等…

SELECT Orders.id, 
  sum(Quantity) AS "Box Count"
  FROM OrderLines
    INNER JOIN Orders ON OrderLines.Order_id = Orders.id
    INNER JOIN Products ON OrderLines.Product_id = Products.id
  WHERE (Products.ProductType_id = 2) AND Orders.shipdate > '10/01/2014'
GROUP BY Orders.id

它正在返回这个：

   id  Box Count
-----  ---------
68015          6
69660          3
70923          3
72697         13

我希望它返回这个：

Box Count  Total Count
---------  -----------
        3            2
        6            1
       13            1

Answer 1

您可以使用第二个查询：

SELECT 
[Box Count]
,COUNT(*) AS [Total Count]
FROM
(
    SELECT 
    Orders.id, 
    sum(Quantity) AS "Box Count" 
    FROM OrderLines 
    INNER JOIN Orders ON OrderLines.Order_id = Orders.id 
    INNER JOIN Products ON OrderLines.Product_id = Products.id 
    WHERE (Products.ProductType_id = 2) AND Orders.shipdate > '10/01/2014' 
    GROUP BY Orders.id
) AS InnerQuery
GROUP BY [Box Count]
ORDER BY COUNT(*) DESC -- optional