用表1的总数量减去表2

Question

伙计们，我有这个问题在减去两个表(Deliveries和Returns)由其CONTAINER_TYPE和CUSTOMER_NAME分组的TOTAL_QUANTITY，这个示例查询返回0的Customer_Balance列，如果我减去例如5 - 0，但如果我减去5 -1，它返回4。

select TOTAL_DELIVERY.CUSTOMER_NAME,
    TOTAL_DELIVERY.CONTAINER_TYPE,
    coalesce(TOTAL_DELIVERY.TOTAL_QUANTITY, 0) as TOTAL_DELIVERY,
    coalesce(TOTAL_PULLOUT.TOTAL_QUANTITY, 0) as TOTAL_PULLOUT,
    coalesce((TOTAL_DELIVERY.TOTAL_QUANTITY - TOTAL_PULLOUT.TOTAL_QUANTITY), 0) as CUSTOMER_BALANCE
from TOTAL_DELIVERY
left join TOTAL_PULLOUT
on TOTAL_DELIVERY.CUSTOMER_NAME = TOTAL_PULLOUT.CUSTOMER_NAME
and TOTAL_DELIVERY.CONTAINER_TYPE = TOTAL_PULLOUT.CONTAINER_TYPE

Deliveries Table (View)
================
----------------------------------------------
CUSTOMER_NAME| CONTAINER_TYPE | TOTAL_QUANTITY
-------------+----------------+---------------
Bryan        | Slim           | 5
-------------+----------------+---------------
Bryan        | Jug            | 5
-------------+----------------+---------------

Returns Table (View)
=============
CUSTOMER_NAME| CONTAINER_TYPE| TOTAL_QUANTITY
-------------+---------------+---------------
Bryan        | Slim          | 5
-------------+---------------+---------------
Expected output
===============
Customer | Container | Total_Delivery | Total_Return | Customer_Balance |
---------+-----------+----------------+--------------+------------------ 
Bryan    | Slim      | 5              | 5            | 0                |
---------+-----------+----------------+--------------+------------------
Bryan    | Jug       | 5              | 0            | 5                |
---------+-----------+----------------+---------------------------------
My INCORRECT Result output (incorrect result at the bottom right cell)
=========
Customer | Container | Total_Delivery | Total_Return | Customer_Balance |
---------+-----------+----------------+--------------+------------------ 
Bryan    | Slim      | 5              | 5            | 0                |
---------+-----------+----------------+--------------+------------------
Bryan    | Jug       | 5              | 0            | 0                |
---------+-----------+----------------+---------------------------------

你能告诉我我做错了什么吗?谢谢你！

Answer 1

(TOTAL_DELIVERY.TOTAL_QUANTITY - TOTAL_PULLOUT.TOTAL_QUANTITY)

必须是

(coalesce(TOTAL_DELIVERY.TOTAL_QUANTITY,0) - coalesce(TOTAL_PULLOUT.TOTAL_QUANTITY, 0))