Search code examples
sqlsap-ase

SQL: sum(unit) table a minus sum(unit) table b

I have 2 tables;

  1. item_in(item_id,unit)
  2. item_out(item_id,unit)

Now let say I want to know how many unit is being insert for every item, I just query

select sum(unit) from item_in order by item_id

Likewise, if I want to know how many unit is being taken out, I just query

select sum(unit) from item_out order by item_id

I don't know how to query the balance (item_in - item_out) for each item.

It is great if I can make all the query in one stored procedure, because I want to call the procedure using DataWindow.

Please help, thank you.

Solution 
  • SELECT
  item_id,
  SUM(unit) AS unit_balance
FROM (
  SELECT item_id, unit FROM item_in
  UNION ALL
  SELECT item_id, -unit FROM item_out
) AS s (item_id, unit)
GROUP BY item_id