如何为两个包含不同表字段的不同表计算求和

Question

我有两个表"tbl_In_Details"one_answers"tbl_Out_Detailstbl_In_Details 的样本数据

ID  Item_Name   Rate    Quantity    Source_company  
1   wire         10        4        2020-04-21 22:47:29.083 
2   Tea          4         20       2020-04-21 22:47:52.823 
Sample data for tbl_Out_Details
ID  Item_Name   Quantity    Created_Date
1     wire        1       2020-04-21 22:48:48.233   
2     wire        2       2020-04-21 22:50:16.367   
3     Tea         2       2020-04-21 23:48:39.943

现在我想从下表中计算价格和价格的总和(RATE*QUANTITY(我试过这样的方法，但没有得到的结果

select o.Item_Name, SUM(o.quantity) as Total_quantity ,SUM(o.quantity * i.Rate) as Expenses
from tbl_In_Details i inner join tbl_Out_Details o
ON i.Item_Name = o.Item_Name group  by o.Item_Name,o.quantity, i.Rate

我的输出应该是

Item_Name   Total_quantity  Expenses
Tea           2               8
wire          3               30

Answer 1

您的查询完全正常。你只需要删除按"数量"one_answers"费率"分组就可以得到想要的结果。

select o.Item_Name, SUM(o.quantity) as Total_quantity ,SUM(o.quantity * i.Rate) as Expenses
from tbl_In_Details i inner join tbl_Out_Details o
ON i.Item_Name = o.Item_Name group  by o.Item_Name;

Answer 2

一种方法使用union all和group by：加入前的骨料：

select i.item_name, o.quantity, i.expenses
from (select id, item_name, sum(rate*quantity) as expenses
from tbl_In_Details
group by id, item_name
) i join
(select id, item_name, sum(quantity) as quantity
from tbl_Out_Details
group by id, item_name
) o
on i.id = o.id