CODEWITHSUNDEEP
phpmysql
Jitu
Jitu

Reputation: 11

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

Upvotes: 1

Views: 198

Answers (5)

Galz
Galz

Reputation: 6832

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;

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

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

Upvotes: 1

kaizer1v
kaizer1v

Reputation: 908

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

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41549

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

Upvotes: 1

user554546
user554546

Reputation:

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
)

Upvotes: 1

Related Questions