Subtract 1 query's result from another query's result

Query1:

SELECT sum(quantity) 
FROM `tbl_stock` 
WHERE item_id = '4' 
 开发者_开发技巧 AND div_id = '1' 
  AND internal_reel_no = 'DP1000585' 
  AND txn_type IN ('IN')
GROUP BY div_id, item_id, txn_type

Query2:

SELECT sum(quantity) 
FROM `tbl_stock` 
WHERE item_id = '4' 
  AND div_id = '1' 
  AND internal_reel_no = 'DP1000585' 
  AND txn_type IN ('IS')
GROUP BY div_id, item_id, txn_type

I want to subtract sum(quantity) of Query1 to sum(quantity) of Query2.

Please let me know the answer

Here is another option, which is more general and does not involve any of the original queries logic:

SELECT sum(quantity) INTO @sum1
FROM `tbl_stock` 
WHERE item_id = '4' 
  AND div_id = '1' 
  AND internal_reel_no = 'DP1000585' 
  AND txn_type IN ('IN')
GROUP BY div_id, item_id, txn_type;

SELECT sum(quantity) INTO @sum2
FROM `tbl_stock` 
WHERE item_id = '4' 
  AND div_id = '1' 
  AND internal_reel_no = 'DP1000585' 
  AND txn_type IN ('IS')
GROUP BY div_id, item_id, txn_type;

SELECT @sum1- @sum2;

Subqueries.

    select q1_sum-q2_sum
    from( SELECT sum(quantity) as q1_sum
FROM `tbl_stock` 
WHERE item_id = '4' 
  AND div_id = '1' 
  AND internal_reel_no = 'DP1000585' 
  AND txn_type IN ('IN')
GROUP BY div_id, item_id, txn_type
    cross join
    SELECT sum(quantity) as q2_sum
FROM `tbl_stock` 
WHERE item_id = '4' 
  AND div_id = '1' 
  AND internal_reel_no = 'DP1000585' 
  AND txn_type IN ('IN')
GROUP BY div_id, item_id, txn_type
)

I would try:

SELECT 
    sum(case when txn_type = 'IN' then quantity else 0 end) 
    - sum(case when txn_type = 'IS' then quantity else 0 end) 
FROM `tbl_stock` 
WHERE item_id = '4' 
  AND div_id = '1' 
  AND internal_reel_no = 'DP1000585' 
  AND txn_type IN ('IN','IS')
GROUP BY div_id, item_id

Note: This would work in MSSQL - you'd have to try it in MySQL

SELECT SUM( CASE WHEN txn_type = 'IN' THEN quantity
                 WHEN txn_type = 'IS' THEN -quantity
                 END
          ) AS result
FROM
  ( SELECT txn_type, SUM(quantity) AS quantity
    FROM tbl_stock 
    WHERE item_id = '4' 
      AND div_id = '1' 
      AND internal_reel_no = 'DP1000585' 
      AND txn_type IN ('IN', 'IS')
    GROUP BY div_id, item_id, txn_type
  ) AS grp

SELECT sum(quantity)
FROM tbl_stock
WHERE item_id='4'
AND div_id='1'
AND internal_reel_no='DP1000585'
AND txn_type IN ('IS') OR txn_type IN ('IN')
GROUP BY div_id, item_id, txn_type