mySQL - Count 和 SUM 在同一行

Question

我有 2 个表 - Transaction 和 TransactionEntry。 Transaction 包含时间，TransactionEntry 包含售出的商品。

一个Transaction可以有多个TransactionEntry

我想找到 2 个日期内的所有 TransactionEntry.ItemID 并对 TransactionEntry.Quantity 求和。然后 GROUP BY TransactionEntry.ItemID。

此代码有效并给出预期结果:

SELECT TransactionEntry.ItemID AS iID, SUM(TransactionEntry.Quantity)
FROM Transaction INNER JOIN TransactionEntry ON TransactionEntry.TransactionNumber = Transaction.TransactionNumber
WHERE Transaction.Time > '2015-07-27 00:00:00' AND Transaction.Time < '2015-08-02 23:59:59'
GROUP BY TransactionEntry.ItemID
ORDER BY SUM(TransactionEntry.Quantity) DESC

不知道怎么建表就画了个例子哈哈:

 -----------------
| iID   | SUM(T.. |
|-----------------| 
| 30419 | 58      |
| 30420 | 50      |
| 55416 | 36      |
| ...   | ...     |
 -----------------

我的问题是我想要第 3 列，它告诉我有多少交易构成了 SUM。使用上面的示例，iID 30419 售出 58 次，但这可能卖给了 58 个不同的人或只有 1 个人。我想知道这个。

以下 SQL 有效:

SELECT COUNT(TransactionEntry.ID) 
FROM Transaction INNER JOIN TransactionEntry ON TransactionEntry.TransactionNumber = Transaction.TransactionNumber 
WHERE Transaction.Time > '2015-07-27 00:00:00' AND Transaction.Time < '2015-08-02 23:59:59' AND TransactionEntry.ItemID = 10791

但我不确定如何将两者结合起来，如果这有意义的话。

我尝试了以下但超时:

SELECT TransactionEntry.ItemID AS iID, SUM(TransactionEntry.Quantity), (SELECT COUNT(TransactionEntry.ID) FROM Transaction INNER JOIN TransactionEntry ON TransactionEntry.TransactionNumber = Transaction.TransactionNumber WHERE Transaction.Time > '2015-07-27 00:00:00' AND Transaction.Time < '2015-08-02 23:59:59' AND TransactionEntry.ItemID = iID) AS C
FROM Transaction INNER JOIN TransactionEntry ON TransactionEntry.TransactionNumber = Transaction.TransactionNumber
WHERE Transaction.Time > '2015-07-27 00:00:00' AND Transaction.Time < '2015-08-02 23:59:59'
GROUP BY TransactionEntry.ItemID
ORDER BY SUM(TransactionEntry.Quantity) DESC

我追求的是可能的吗？

谢谢

Answer 1

你能试试这个吗:

SELECT T.ItemID
    , SUM(TE.Quantity)
    , COUNT(TE.ID) 
    FROM Transaction T
    INNER JOIN TransactionEntry TE ON TE.TransactionNumber = T.TransactionNumber
    WHERE T.Time BETWEEN '2015-07-27 00:00:00' AND '2015-08-02 23:59:59'
    GROUP BY T.ItemID