How to SUM and SUBTRACT using SQL?

Question

I am using MySQL and I have two tables:

master_table

• ORDERNO
• ITEM
• QTY

stock_bal

• ITEM
• BAL_QTY

Master table has duplicate ORDERNO and ITEM values. I have get total QTY using SQL 'GROUP BY' clause.

I need to deduct/subtract BAL_QTY from SUM of ITEM (master_table). I've got SUM QTY value using query (actually there are many rows).

Answer 1

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_BAL FROM     master_table INNER JOIN          stock_bal ON master_bal.ITEM = stock_bal.ITEM GROUP BY master_table.ORDERNO,          master_table.ITEM 

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

UPDATE stock_bal SET    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