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

Devin Burke · Accepted Answer · 2011-07-16T03:54:07.057

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

this is very helpful and working fine,, thanks a lot.. also i need to update master table QTY value. this value came from many rows.. how can i do this... — Tharindu ucsc, Jul 16 '11 at 05:15

Alex Khimich · Answer 2 · 2023-06-28T17:15:48.347

11

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 sum(items_2) from items_table_1 where ...) 

as difference

edited Jun 28 '23 at 17:15

answered Oct 14 '19 at 07:42

Alex Khimich

788
9
10

score 4 · Answer 3 · answered Jun 08 '19 at 15:44

4

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

answered Jun 08 '19 at 15:44

Rumado

165
2
16

score 3 · Answer 4 · answered Jul 16 '11 at 03:31

3

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

answered Jul 16 '11 at 03:31

Paul

139,544
27
275
264

Tory Netherton · Answer 5 · 2011-07-16T03:51:42.277

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

score 1 · Answer 6 · answered Jul 30 '15 at 06:19

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

How to SUM and SUBTRACT using SQL?

6 Answers6

Linked