SQL SUM function to get inventory balance

Question

UPDATE :

I have 2 locations to sell products , each location has 1 table ... i got balance for each location shown in image below ... now I want to Sum balances in one balance as total, I want to get full Balance for items (SUM balance for 2 locations ) ... need your help

I used this query to get balance shown above

Select item_id , sum(purchase)-sum(sales)as total from location_1  group by item_id 
HAVING sum(purchase)-sum(sales) <> 0
UNION ALL
Select item_id,  sum(purchase)-sum(sales) as total from location_2  group by item_id, 
HAVING sum(purchase)-sum(sales) <> 0 

Answer 1

You could try to get rid of the number at the end of the itemid column from the tables, then union them.

SELECT t.itemid, sum(purchase)- sum(sales) as balance
FROM (
      SELECT left(itemid, length(itemid) -1) as itemid, 
             sales, 
             purchase 
      FROM Location_1
      UNION ALL 
      SELECT left(itemid, length(itemid) -1) as itemid,
             sales,
             purchase
      FROM Location_2
     ) AS t
     GROUP BY t.itemid;

db fiddle link