CODEWITHSUNDEEP
sqlsql-server
RAJEEV RANJAN
RAJEEV RANJAN

Reputation: 25

How to calculate Sum and Subtraction from two different table

I have two table "tbl_In_Details" and "tbl_Out_Details" Sample data for tbl_In_Details

sample data for tbl_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.94

Now i want to calculate current stock i.e. (incoming - Outgoing) as Current_Stock i tried in such a way but not getting proper record please guide me where i need to modify my query:

Select O.Item_Name, 
sum(CAST(I.Quantity AS INT))as Incoming_Quantity , 
 SUM(CAST(o.Quantity as int)) as Outgoing_Quantity , 
(sum(CAST(I.Quantity AS INT)) - SUM(CAST(o.Quantity AS INT))) As Current_Stock
from tbl_In_Details I inner join tbl_Out_Details O
ON I.Item_Name = o.Item_Name group by O.Item_Name

My output should be

Item_Name   Incoming_Quantity   Outgoing_quantity   Current_Stock
  Wire          4                  3                      1
  Tea           20                 2                      18

Upvotes: 1

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

Use union all and aggregation:

select item_name, sum(in_quantity) as in_quantity, sum(out_quantity) as out_quantity,
       sum(in_quantity) - sum(out_quantity) as in_stock
from ((select item_name, quantity as in_quantity, 0 as out_quantity
       from incoming
      ) union all
      (select item_name, 0, quantity 
       from outgoing
      ) 
     ) io
group by item_name;

Upvotes: 1

Related Questions