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

Solution

Method 1 – SQL Join and Aggregation 1

Intuition

To count the total apples and oranges, we need to sum the apples and oranges in each box, and if a box contains a chest, also add the apples and oranges from the chest. We can do this by joining the Boxes and Chests tables and using COALESCE to handle boxes without a chest.

Approach

LEFT JOIN the Boxes table with the Chests table on chest_id.
For each box, sum its apple_count and orange_count with the corresponding chest's apple_count and orange_count (if any).
Aggregate the sums over all boxes.
Return the total apple_count and orange_count.

Code

[{"language":"MySQL","code":"SELECT\n  SUM(b.apple_count + COALESCE(c.apple_count, 0)) AS apple_count,\n  SUM(b.orange_count + COALESCE(c.orange_count, 0)) AS orange_

Related Tags