sql - DB2 Select from two tables when one table requires sum -

in db2 database, want following simple mathematics using sql query:

availablestock = supplystock - demandstock

supplystock stored in 1 table in 1 row, let's call table supply table. supply table has data:

productid | supplystock --------------------- 109            10 244             7  edit: exclude product search 

demandstock stored in separate table demand, demand logged each customer logs demand during customer order journey. example data demand table:

productid | demandstock  ------------------------ 109            1 244            4  edit: exclude product 109            6 109            2 

so in our heads, if want calculate availablestock product '109', supply 10, demand product 109 totals 9, , available stock 1.

how do in one select query in db2 sql?

the knowledge have far of of imagined steps in pseudocode:

• i select supplystock product id = '109'
• i select sum(demandstock) product id = '109'
• i subtract supplystock demandstock
• i present resulting availablestock

the results this:

product id | availablestock 109                9 

i'd love selected in 1 sql select query.

edit: i've since received answer (that perfect) , realised question missed out information. information: need exclude data products don't want select data for, , need select product 109. apologies, omitted original question. i've since added 'where' select product , works me. future sake, perhaps answer should include information too.

you using join bring tables , group by aggregate results of join:

select s.productid, s.supplystock, sum(d.demandstock),        (s.supplystock - sum(d.demandstock)) available supply s left join      demand d      on s.productid = d.productid  s.productid = 109 group s.productid, s.supplystock;