MySQL - 一个表的SUM正在加入另一个表

Question

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 :)

Answer 1

Add the following to the end of your query:

GROUP BY items.itemID

The reason this is happening is because the SQL engine doesn't know that you wanted to sum the amount column per itemId. It simply sums them all. If you want to change this behavior, well that's what GROUP BY is for. And this holds true for all other aggregate functions as well (MIN, MAX, COUNT, etc).

You might say to yourself, "If it doesn't know how to group by what I told it to select (SELECT itemId ...), then why does it return the value 1?". That's a great question, and the value returned is indeed going to be unpredictable for the most part. It may in fact at some point start returning 2 or some other itemId in your table. For this reason, many SQL engines don't allow you to specify non aggregates that are not included in your GROUP BY clause - that is, they will throw an error if you try this. MySQL doesn't care about this quandary and returns a result, as unpredictable as it may be.