Menu
I am using MySQL and I have two tables:
master_table
stock_bal
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).
596
Multiply the subtract from data by (-1) and then sum() the both amount then you will get subtracted amount. Highly active question.
The Minus Operator in SQL is used with two SELECT statements. The MINUS operator is used to subtract the result set obtained by first SELECT query from the result set obtained by second SELECT query.
Example - With Single ExpressionSELECT SUM(salary) AS "Total Salary" FROM employees WHERE salary > 25000; In this SQL SUM Function example, we've aliased the SUM(salary) expression as "Total Salary". As a result, "Total Salary" will display as the field name when the result set is returned.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
