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

User · Answer

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

User · Answer

An example for subtraction is given below   Select value1 -  select value2 from AnyTable1  from AnyTable2   value1  amp  value2 can be  count sum average output etc  But the values should be comapatible

User · Answer

Simple copy  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] How to SUM and SUBTRACT using SQL?

Examples related to mysql

Examples related to sql

Examples related to aggregate-functions