CODEWITHSUNDEEP
mysql
DrWalt Bishop
DrWalt Bishop

Reputation: 59

How to subtract a value from a sum of total in MySql LEFT JOIN Query

I have 2 tables.

SELECT * FROM purchases;
+------+---------+-----------+
|purid | total_q | dstatus   |
+------+---------+-----------+
|  1   |    45   | DELIVERED |
|  2   |    50   | LOADING   |
|  3   |    24   | DELIVERED |
|  4   |    15   | DELIVERED |
|  5   |    10   | DELIVERED |
+------+---------------------+

SELECT * FROM warehouse;
+------+-------+---------+
| wid  | purid | total_q |
+------+-------+---------+
|  4   |   1   |    45   |
|  5   |   4   |    15   |
|  9   |   3   |    10   |
|  12  |   3   |     5   |
+------+-------+---------+

I want to get "delivered" purchases with its amounts which are not already included in warehouse table. Here is the demo where I stuck: DEMO

The query which I use is:

SELECT p.purid as purid, (p.total_q - IFNULL(w.total_q,0)) as ntq
FROM `purchases` as p
LEFT JOIN `warehouse` as w ON p.purid=w.purid
WHERE p.dstatus = "DELIVERED" AND (p.total_q - IFNULL(w.total_q,0)) > 0

My desired output:

+-------+------+
| purid | ntq  |
+-------+------+
|  5    |  10  |
|  3    |   9  |
+------+-------+

The problem is I could not subtract "total_q (24) from purchases table" from "sum total_q(10+5) from warehouse table".

Upvotes: 1

Views: 109

Answers (1)

D-Shih
D-Shih

Reputation: 46239

You can try to use subquery aggregate warehouse by purid before join otherwise you might get multiple rows.

Query #1

SELECT p.purid as purid, 
      p.total_q - IFNULL(w.total_q,0) as ntq
FROM `purchases` as p
LEFT JOIN (
  SELECT purid,SUM(total_q) total_q
  FROM warehouse
  GROUP BY purid
) as w ON p.purid=w.purid
WHERE p.dstatus = "DELIVERED" 
AND p.total_q - IFNULL(w.total_q,0) > 0;
purid ntq
3 9
5 10

View on DB Fiddle

Upvotes: 1

Related Questions