MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE total 
(
id int,
name nvarchar(10),
vendor nvarchar(10),
stock nvarchar(10),
area nvarchar(10)
);

insert into total
values
(1,'test1','vendor1','5','1'),
(2,'test2','vendor2','2','2'),
(3,'test3','vendor3','3','3'),
(4,'test4','vendor4','1','4'),
(5,'test5','vendor5','4','5');

select *
from total;


id
name
vendor
stock
area


1
test1
vendor1
5
1

2
test2
vendor2
2
2

3
test3
vendor3
3
3

4
test4
vendor4
1
4

5
test5
vendor5
4
5


CREATE TABLE warehouse 
(
total_id int,
name nvarchar(10),   
vendor nvarchar(10),   
num int,  
outnum int,  	
purchase_at date,	
ship_at date
);

insert into warehouse
values
(1,'test1','vendor1',2,0,'2020-09-02',null),
(1,'test1','vendor1',3,0,'2020-09-02',null),
(3,'test3','vendor3',1,0,'2020-09-02',null),
(3,'test3','vendor3',2,0,'2020-08-05',null),
(3,'test3','vendor3',1,0,'2020-09-02',null),
(3,'test3','vendor3',0,3,null,'2020-08-05'),
(3,'test3','vendor3',0,4,null,'2020-09-04');

select *
from warehouse;


total_id
name
vendor
num
outnum
purchase_at
ship_at


1
test1
vendor1
2
0
2020-09-02
null

1
test1
vendor1
3
0
2020-09-02
null

3
test3
vendor3
1
0
2020-09-02
null

3
test3
vendor3
2
0
2020-08-05
null

3
test3
vendor3
1
0
2020-09-02
null

3
test3
vendor3
0
3
null
2020-08-05

3
test3
vendor3
0
4
null
2020-09-04


select A.area,A.id,A.name,A.vendor,A.stock,
B.num,C.outnum
from total as A
left join (
select total_id,sum(num) as num
from warehouse
where purchase_at like '2020-09%'
group by total_id,date_format(purchase_at,120)
) as B on B.total_id=A.id
left join (
select total_id,sum(outnum) as outnum
from warehouse
where ship_at like '2020-09%'
group by total_id,date_format(ship_at,120)
) as C on C.total_id=A.id
where A.area>='1'
and A.area<='4'
order by A.area,A.id


area
id
name
vendor
stock
num
outnum


1
1
test1
vendor1
5
5
null

2
2
test2
vendor2
2
null
null

3
3
test3
vendor3
3
2
4

4
4
test4
vendor4
1
null
null

id	name	vendor	stock	area
1	test1	vendor1	5	1
2	test2	vendor2	2	2
3	test3	vendor3	3	3
4	test4	vendor4	1	4
5	test5	vendor5	4	5

total_id	name	vendor	num	outnum	purchase_at	ship_at
1	test1	vendor1	2	0	2020-09-02	null
1	test1	vendor1	3	0	2020-09-02	null
3	test3	vendor3	1	0	2020-09-02	null
3	test3	vendor3	2	0	2020-08-05	null
3	test3	vendor3	1	0	2020-09-02	null
3	test3	vendor3	0	3	null	2020-08-05
3	test3	vendor3	0	4	null	2020-09-04