# $1715. 苹果和橘子的个数
# https://leetcode.cn/problems/count-apples-and-oranges/

# SQL架构
Create table If Not Exists Boxes (box_id int, chest_id int, apple_count int, orange_count int);
Create table If Not Exists Chests (chest_id int, apple_count int, orange_count int);
Truncate table Boxes;
insert into Boxes (box_id, chest_id, apple_count, orange_count) values ('2', null, '6', '15');
insert into Boxes (box_id, chest_id, apple_count, orange_count) values ('18', '14', '4', '15');
insert into Boxes (box_id, chest_id, apple_count, orange_count) values ('19', '3', '8', '4');
insert into Boxes (box_id, chest_id, apple_count, orange_count) values ('12', '2', '19', '20');
insert into Boxes (box_id, chest_id, apple_count, orange_count) values ('20', '6', '12', '9');
insert into Boxes (box_id, chest_id, apple_count, orange_count) values ('8', '6', '9', '9');
insert into Boxes (box_id, chest_id, apple_count, orange_count) values ('3', '14', '16', '7');
Truncate table Chests;
insert into Chests (chest_id, apple_count, orange_count) values ('6', '5', '6');
insert into Chests (chest_id, apple_count, orange_count) values ('14', '20', '10');
insert into Chests (chest_id, apple_count, orange_count) values ('2', '8', '8');
insert into Chests (chest_id, apple_count, orange_count) values ('3', '19', '4');
insert into Chests (chest_id, apple_count, orange_count) values ('16', '19', '19');

# Write your MySQL query statement below
select
    ifnull(sum(b.apple_count), 0) + ifnull(sum(c.apple_count), 0) as apple_count,
    ifnull(sum(b.orange_count), 0) + ifnull(sum(c.orange_count), 0) as orange_count
from
    Boxes b
    left join Chests c on b.chest_id = c.chest_id;

# clean-up
drop table Boxes;
drop table Chests;