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

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

CodePudding user response：

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