Question

1715. Count Apples and Oranges

中文文档

Description

Table: Boxes

+--------------+------+
| Column Name  | Type |
+--------------+------+
| box_id     | int  |
| chest_id   | int  |
| apple_count  | int  |
| orange_count | int  |
+--------------+------+
box_id is the column with unique values for this table.
chest_id is a foreign key (reference column) of the chests table.
This table contains information about the boxes and the number of oranges and apples they have. Each box may include a chest, which also can contain oranges and apples.

 

Table: Chests

+--------------+------+
| Column Name  | Type |
+--------------+------+
| chest_id   | int  |
| apple_count  | int  |
| orange_count | int  |
+--------------+------+
chest_id is the column with unique values for this table.
This table contains information about the chests and the corresponding number of oranges and apples they have.

 

Write a solution to count the number of apples and oranges in all the boxes. If a box contains a chest, you should also include the number of apples and oranges it has.

The result format is in the following example.

 

Example 1:

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

Solutions

Solution 1

# Write your MySQL query statement below
SELECT
  SUM(IFNULL(b.apple_count, 0) + IFNULL(c.apple_count, 0)) AS apple_count,
  SUM(IFNULL(b.orange_count, 0) + IFNULL(c.orange_count, 0)) AS orange_count
FROM
  Boxes AS b
  LEFT JOIN Chests AS c ON b.chest_id = c.chest_id;