MySQL 如何使用 SQL 进行 SUM 和 SUBTRACT？

How to SUM and SUBTRACT using SQL?

提问by Tharindu ucsc

I am using MySQL and I have two tables:

我正在使用 MySQL，我有两个表：

master_table

master_table

• ORDERNO
• ITEM
• QTY

• 订单号
• 物品
• 数量

stock_bal

stock_bal

• ITEM
• BAL_QTY

• 物品
• BAL_QTY

Master table has duplicate ORDERNOand ITEMvalues. I have get total QTYusing SQL 'GROUP BY' clause.

主表有重复ORDERNO和ITEM值。我已经QTY使用 SQL 'GROUP BY' 子句获得了总数。

I need to deduct/subtract BAL_QTYfrom SUM of ITEM(master_table). I've got SUM QTYvalue using query (actually there are many rows).

我需要BAL_QTY从ITEM(master_table) 的SUM 中扣除/减去。我QTY使用查询获得了 SUM值（实际上有很多行）。

回答by Devin Burke

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

我想这就是你要找的。NEW_BAL是QTY从余额中减去 s的总和：

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

回答by Paul

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

回答by Alex Khimich

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

带有子查询的简单复制和粘贴示例，请注意，两个查询都应返回 1 行：

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

as difference

回答by Tory Netherton

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

如果你真的需要这样做......假设 ITEM 在 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

回答by Binaya Shrestha

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.

我尝试过这种技术。将数据中的减法乘以 (-1) 然后 sum() 将两者的数量相乘，然后您将得到减去的数量。

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

回答by Rumado

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

value1 & value2 可以是计数、求和、平均输出等。但值应该是兼容的