I have a table like this:

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:

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.

CodePudding user response：

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;