How to SUM and SUBTRACT using SQL?

I think this is what you're looking for. NEW_BAL is the sum of QTYs subtracted from the balance:

SELECT   master_table.ORDERNO,         master_table.ITEM,         SUM(master_table.QTY),         stock_bal.BAL_QTY,         (stock_bal.BAL_QTY - SUM(master_table.QTY)) AS NEW_BALFROM     master_table INNER JOIN         stock_bal ON master_bal.ITEM = stock_bal.ITEMGROUP BY master_table.ORDERNO,         master_table.ITEM

If you want to update the item balance with the new balance, use the following:

UPDATE stock_balSET    BAL_QTY = BAL_QTY - (SELECT   SUM(QTY)                            FROM     master_table                            GROUP BY master_table.ORDERNO,                                     master_table.ITEM)

This assumes you posted the subtraction backward; it subtracts the quantities in the order from the balance, which makes the most sense without knowing more about your tables. Just swap those two to change it if I was wrong:

(SUM(master_table.QTY) - stock_bal.BAL_QTY) AS NEW_BAL

Simple copy & paste example with subqueries,Note, that both queries should return 1 row:

select(select sum(items_1) from items_table_1 where ...)-(select count(items_2) from items_table_1 where ...) as difference

I'm not sure exactly what you want, but I think it's along the lines of:

SELECT `Item`, `qty`-`BAL_QTY` as `qty` FROM ((SELECT Item, SUM(`QTY`) as qty FROM `master_table` GROUP BY `ITEM`) as A NATURAL JOIN `stock_table`) as B