Monday, 26 August 2013

MySQL - SUM for one table is joining another table

MySQL - SUM for one table is joining another table

I have 2 tables:
1.items
+-------+---------+
|itemID | itemName|
+-----------------+
|1 | Item1 |
|2 | Item2 |
+-------+---------+
2.purchases
+-----------+--------+--------+-----------+
|purchaseID | userID | itemID | itemAmount|
+-----------+--------+--------+-----------+
|1 | 1 | 1 | 3 |
|2 | 2 | 1 | 4 |
|3 | 1 | 2 | 5 |
+-----------+--------+--------+-----------+
A simplified version of my MySQL code is this:
SELECT
items.itemID,
SUM(purchases.itemAmount)
FROM items
LEFT OUTER JOIN purchases ON items.itemID = purchases.itemID
I would like the result to be this:
itemID | itemAmount
1 | 7 2 | 5
But instead, this is the result:
itemID | itemAmount
1 | 12
Why is this, and how can I fix it? Thanks :)

No comments:

Post a Comment