I have a table like this:
| id | pallet_id | action | product_id | qty |
|---|---|---|---|---|
| 1 | 2 | ADD | 1 | 100 |
| 2 | 2 | ADD | 1 | 50 |
| 3 | 2 | REMOVE | 1 | 30 |
| 4 | 1 | ADD | 2 | 200 |
| 5 | 1 | ADD | 1 | 10 |
and what I need is to display available qty for product_id = 1 AND GROUP per-pallet_id. The formula is simple, sum which action is "ADD" – "REMOVE" per-pallet_id, so the result should be like this:
| id | pallet_id | available qty |
|---|---|---|
| 1 | 2 | 120 |
| 2 | 1 | 10 |
What I have did:
SELECT ((SELECT sum(DISTINCT qty) as ADDING FROM table WHERE action IN ('ADD') AND product_id IN (1)) - ifnull((SELECT sum(DISTINCT qty) as REMOVING FROM table WHERE action IN ('REMOVE') AND product_id IN (1)),0)) as avaiable_qty, pallet_id FROM table WHERE product_id IN (1) GROUP BY pallet_id ORDER BY product_id DESC
Then the result is same for those 2 rows: available qty is 120 each. So I believe I must add "GROUP BY". I added became like this:
SELECT ((SELECT sum(DISTINCT qty) as ADDING FROM table WHERE action IN ('ADD') AND product_id IN (1) GROUP BY pallet_id) - ifnull((SELECT sum(DISTINCT qty) as REMOVING FROM table WHERE action IN ('REMOVE') AND product_id IN (1) GROUP BY pallet_id),0)) as avaiable_qty, pallet_id FROM table WHERE product_id IN (1) GROUP BY pallet_id ORDER BY product_id DESC
But MySQL return #1242 subquery returns more than 1 row
Trying to search on google but didn’t found similar case with similar table structure.
I really appreciate your help.
>Solution :
Use conditional aggregation:
SELECT pallet_id,
SUM(qty * CASE action WHEN 'ADD' THEN 1 WHEN 'REMOVE' THEN -1 END) AS available_qty
FROM tablename
WHERE product_id IN (1) -- or product_id = 1
GROUP BY pallet_id;