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).

6 Answers

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

1

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

An example for subtraction is given below:

Select value1 - (select value2 from AnyTable1) from AnyTable2

value1 & value2 can be count,sum,average output etc. But the values should be comapatible

ah homework...

So wait, you need to deduct the balance of items in stock from the total number of those items that have been ordered? I have to tell you that sounds a bit backwards. Generally I think people do it the other way round. Deduct the total number of items ordered from the balance.

If you really need to do that though... Assuming that ITEM is unique in stock_bal...

SELECT s.ITEM, SUM(m.QTY) - s.QTY AS result
FROM stock_bal s
INNER JOIN master_table m ON m.ITEM = s.ITEM
GROUP BY s.ITEM, s.QTY

I have tried this kind of technique. Multiply the subtract from data by (-1) and then sum() the both amount then you will get subtracted amount.

-- Loan Outstanding select 'Loan Outstanding' as Particular, sum(Unit), sum(UptoLastYear), sum(ThisYear), sum(UptoThisYear) from ( select sum(laod.dr) as Unit, sum(if(lao.created_at <= '2014-01-01',laod.dr,0)) as UptoLastYear, sum(if(lao.created_at between '2014-01-01' and '2015-07-14',laod.dr,0)) as ThisYear, sum(if(lao.created_at <= '2015-07-14',laod.dr,0)) as UptoThisYear from loan_account_opening as lao inner join loan_account_opening_detail as laod on lao.id=laod.loan_account_opening_id where lao.organization = 3 union select sum(lr.installment)*-1 as Unit, sum(if(lr.created_at <= '2014-01-01',lr.installment,0))*-1 as UptoLastYear, sum(if(lr.created_at between '2014-01-01' and '2015-07-14',lr.installment,0))*-1 as ThisYear, sum(if(lr.created_at <= '2015-07-14',lr.installment,0))*-1 as UptoThisYear from loan_recovery as lr inner join loan_account_opening as lo on lr.loan_account_opening_id=lo.id where lo.organization = 3 ) as t3