How to calculate values from multiple tables?

Question

I want to get the values of quantity and amount from store and order detail tables into one table, but the values are not summing up correctly.

Product table

ProductId	Name
1	Apple
2	Banana

Store table

ID	Name	Qty	Rack	ProductId
1	Banana	5	A	2
2	Apple	3	B	1
3	Banana	2	C	2

OrderDetail

ID	Name	Qty	Amount	ProductId
1	Banana	1	2.00	2
2	Banana	2	4.00	2
3	Apple	1	1.00	1

See code that l had tried

SELECT
    Store.Name,
    Sum(Store.Qty) as Store,
    Sum(OrderDetail.Qty) as QtyOrder,
    Sum(OrderDetail.Amount) as Amount
FROM Store
INNER JOIN OrderDetail ON Store.ProductId = OrderDetail.ProductId
GROUP BY Store.Name, OrderDetail.Name

Expected output

Name	Store	QtyOrder	Amount
Banana	7	3	6.00
Apple	3	1	1.00

Answer 1

Try this:

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

https://dbfiddle.uk/OKmlqzq0