I have problem on adding and subtracting values from different tables using SQL, adding and subtracting works fine, but it returns an INCORRECT values
here is my query to add the "QUANTITY" from table 1(DELIVERIES table) that is grouped by "CONTAINER_TYPE", the same in TABLE 2 (PULLOUT table), I wanted the sum of the quantity of the first table to be subtracted to the sum of the quantity of the second table. but i get and INCORRECT data.
SELECT
CUSTOMER.CUSTOMER_NAME,
DELIVERIES.CONTAINER_TYPE,
COALESCE(SUM(DELIVERIES.QUANTITY), 0) AS TOTAL_DELIVERY,
COALESCE(SUM(PULLOUTS.QUANTITY), 0) AS TOTAL_RETURNS,
COALESCE(SUM(DELIVERIES.QUANTITY), 0) - COALESCE(SUM(PULLOUTS.QUANTITY), 0) AS CUSTOMER_BALANCE
FROM EJPADMIN.CUSTOMER
INNER JOIN DELIVERIES
ON DELIVERIES.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN PULLOUTS
ON PULLOUTS.DELIVERY_ID = DELIVERIES.DELIVERY_ID
GROUP BY CUSTOMER.CUSTOMER_NAME, DELIVERIES.CONTAINER_TYPEguys please help. Thankyou!
DELIVERIES table PULLOUT table CUSTOMER TRANSACTION table (incorrect result)
