SQL Interview Questions – Count Apples and Oranges

Problem Description –

Write an SQL query 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 query result format is in the following example.

Problem Link – Count apples and oranges

Difficulty Level – Medium

Solution –

SELECT
    SUM(b.apple_count  + IFNULL(c.apple_count,0)) as apple_count,
    SUM(b.orange_count + IFNULL(c.orange_count,0)) as orange_count
FROM Boxes as b LEFT JOIN Chests as c
ON b.chest_id = c.chest_id