Andrew C Andrew C - 1 month ago 12
MySQL Question

Is it possible to subtract the values of 2 subqueries and return the difference?

The logic behind this is that I want to subtract the returned products from the sold products. That way I will have a net quantity of products sold. The subqueries return numeric values but I'm not sure how to get the difference from these subqueries.

SELECT COUNT(QUANTITY_ORDERED) FROM PRODUCTS
WHERE
(SELECT COUNT(QUANTITY_ORDERED) FROM PRODUCTS WHERE CATEGORY ='SOLD')-(SELECT COUNT(QUANTITY_ORDERED) FROM PRODUCTS WHERE CATEGORY='RETURNED')

Answer Source

Just do a conditional SUM() instead of COUNT()

SELECT product_id, 
       SUM(CASE WHEN CATEGORY ='SOLD' THEN QUANTITY_ORDERED ELSE 0 END)
     - SUM(CASE WHEN CATEGORY ='RETURNED' THEN QUANTITY_ORDERED ELSE 0 END) as total
FROM products
GROUP BY product_id

OR

SELECT product_id, 
       COUNT(CASE WHEN CATEGORY ='SOLD' THEN 0 END)
     - COUNT(CASE WHEN CATEGORY ='RETURNED' THEN 0 END) as total
FROM products
GROUP BY product_id