Input:
Boxes table:
+--------+----------+-------------+--------------+
| box_id | chest_id | apple_count | orange_count |
+--------+----------+-------------+--------------+
| 2 | null | 6 | 15 |
| 18 | 14 | 4 | 15 |
| 19 | 3 | 8 | 4 |
| 12 | 2 | 19 | 20 |
| 20 | 6 | 12 | 9 |
| 8 | 6 | 9 | 9 |
| 3 | 14 | 16 | 7 |
+--------+----------+-------------+--------------+
Chests table:
+----------+-------------+--------------+
| chest_id | apple_count | orange_count |
+----------+-------------+--------------+
| 6 | 5 | 6 |
| 14 | 20 | 10 |
| 2 | 8 | 8 |
| 3 | 19 | 4 |
| 16 | 19 | 19 |
+----------+-------------+--------------+
Output:
+-------------+--------------+
| apple_count | orange_count |
+-------------+--------------+
| 151 | 123 |
+-------------+--------------+
Explanation:
box 2 has 6 apples and 15 oranges.
box 18 has 4 + 20 (from the chest) = 24 apples and 15 + 10 (from the chest) = 25 oranges.
box 19 has 8 + 19 (from the chest) = 27 apples and 4 + 4 (from the chest) = 8 oranges.
box 12 has 19 + 8 (from the chest) = 27 apples and 20 + 8 (from the chest) = 28 oranges.
box 20 has 12 + 5 (from the chest) = 17 apples and 9 + 6 (from the chest) = 15 oranges.
box 8 has 9 + 5 (from the chest) = 14 apples and 9 + 6 (from the chest) = 15 oranges.
box 3 has 16 + 20 (from the chest) = 36 apples and 7 + 10 (from the chest) = 17 oranges.
Total number of apples = 6 + 24 + 27 + 27 + 17 + 14 + 36 = 151
Total number of oranges = 15 + 25 + 8 + 28 + 15 + 15 + 17 = 123
My answer:
SELECT
SUM(b.apple_count +c.apple_count,0) AS apple_count, # IFNULL
SUM(b.orange_count+c.orange_count,0) AS orange_count
FROM Boxes b
LEFT JOIN Chests c
ON b.chest_id = c.chest_id
The expected answer:
SELECT
SUM(b.apple_count +COALESCE(c.apple_count,0)) AS apple_count, # IFNULL
SUM(b.orange_count+COALESCE(c.orange_count,0)) AS orange_count
FROM Boxes b
LEFT JOIN Chests c
ON b.chest_id = c.chest_id
My question is, why do we have to use COALESCE() OR IFNULL() for this question?
What is the difference between 0 and null when using SUM()?
I thought SUM() is supposed to ignore null values in MySQL and add 6 to apple and 15 to orange even with the null in the chest table?
1 + 0 = 1
1 + null = null
This is the difference.