Andrew C - 1 month ago 12

MySQL Question

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
```