By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with product(id,name) as
(select 1 , 'Apple' union all
select 2, 'Banana'),
store(ID,Name,Qty,Rack,ProductId) as (
select 1,'Banana', 5, 'A', 2 union all
select 2,'Apple', 3, 'B', 1 union all
select 3,'Banana', 2, 'C', 2 ),
OrderDetail(ID,Name,Qty,Amount,ProductId) as (
select 1,'Banana',1,2.00, 2 union all
select 2,'Banana',2,4.00, 2 union all
select 3,'Apple',1,1.00, 1 ),
Thestore as (
select p.Name,
Sum(s.Qty) as Store
from product p
inner join store s on p.id = s.ProductId
GROUP BY p.Name ),
TheOrder as (
select p.Name,
Sum(o.Qty) as QtyOrder,
Sum(o.Amount) as Amount
from product p
inner join OrderDetail o on p.id = o.ProductId
GROUP BY p.Name)
select s.name
,s.store
,o.QtyOrder
,o.Amount
from Thestore s
inner join TheOrder o on s.name = o.name
| name | store | QtyOrder | Amount |
|---|---|---|---|
| Apple | 3 | 1 | 1.00 |
| Banana | 7 | 3 | 6.00 |