将表1的总数量减去表2(Subtracting the total quantity of table 1 to table 2)

伙计们通过按其CONTAINER_TYPE和CUSTOMER_NAME分组的TOTAL_QUANTITY减去两个表（ Deliveries和Returns ）时TOTAL_QUANTITY此问题，如果我减去例如5 - 0 ，则此示例查询返回Customer_Balance列，但如果我减去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 | ---------+-----------+----------------+---------------------------------

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

Guys i have this problem on subtracting the the two tables (Deliveries and Returns) by its TOTAL_QUANTITY grouped by their CONTAINER_TYPE and CUSTOMER_NAME, this sample query returns the column Customer_Balance of 0 if I subtracted for example 5 - 0, but if i subtracted 5 -1 it returns 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 | ---------+-----------+----------------+---------------------------------

can you please tell me what am i doing wrong? thank you!