CODEWITHSUNDEEP
mysql
Lee
Lee

Reputation: 1495

mySQL - Count and SUM on same line

I have 2 tables - Transaction and TransactionEntry. Transaction contains the time and TransactionEntry contains the item(s) sold.

One Transaction can have many TransactionEntry

I want to find all the TransactionEntry.ItemID's within 2 dates and SUM the TransactionEntry.Quantity. Then GROUP BY TransactionEntry.ItemID.

The code for this works and gives the expected result:

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

Not sure how to create a table so I drew the example haha:

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

My problem is that I want a 3rd column, which tells me how many Transactions make up the SUM. Using the example above, iID 30419 sold 58 times but that may have been to 58 different people or to just 1 person. I want to know this.

The following SQL works:

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

But I'm unsure how to combine the 2, if that makes sense.

I have tried the following but it times out:

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

Is what I'm after possible?

Thanks

Upvotes: 1

Views: 186

Answers (1)

KaeL
KaeL

Reputation: 3659

Can you try this one:

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

Upvotes: 1

Related Questions