Substract one subquery from another and then GROUP BY "element

I have this Table:

I'm trying to get the sum of all "entrada" minus all the "salida" by "product_id" im using this query:

SELECT((SELECT SUM(cantidad) FROM inventario WHERE registro = "entrada" GROUP by producto_id)
-(SELECT SUM(cantidad) FROM inventario WHERE registro = "salida" GROUP by producto_id))
FROM inventario GROUP by producto_id

Those subquerys work fine individually but expressed like that returns: #1242 - Subquery returns more than 1 row"

what I'm doing wrong? do I have to make 2 separated querys? and in second thought what if a product has "entrada" but no "salida"?

CodePudding user response：

You don't need a subquery for this; just add or subtract as desired:

select sum(case registro when 'salida' then -cantidad when 'entrada' then cantidad end)
from inventario
group by producto_id

As to what you did wrong, when you use a subquery in an expression or as a select field, it is expecting it to return a single row and single column to be the value. You could do this, by making your subqueries dependent subqueries selecting only data that matches the outer select, e.g. (select sum(candidad) from inventario i where i.registro='entrada' and i.product_id=inventorio.product_id). Then you'd need to also coalesce it to 0 before using it in the subtraction, or products that didn't have both salida and entrada rows would product null.

CodePudding user response：

How about this to get you started -

-- CTE to create dummy data
WITH inventario AS (
  (SELECT 1 AS id, 1 AS producto_id, 'salida' AS registro, 5 AS cantidad) UNION ALL
  (SELECT 2, 1, 'entrada', 15) UNION ALL
  (SELECT 3, 1, 'entrada', 18) UNION ALL
  (SELECT 4, 1, 'entrada', 18) UNION ALL
  (SELECT 5, 3, 'entrada', 14) UNION ALL
  (SELECT 6, 1, 'entrada', 18) UNION ALL
  (SELECT 7, 3, 'entrada', 14) UNION ALL
  (SELECT 8, 1, 'eliminar', 18) UNION ALL
  (SELECT 9, 3, 'salida', 14))
-- end CTE

SELECT
    producto_id,
    SUM(IF(registro = 'entrada', cantidad, 0)) AS `entrada`,
    SUM(IF(registro = 'salida', cantidad, 0)) AS `salida`,
    SUM(IF(registro = 'eliminar', cantidad, 0)) AS `eliminar`,
    SUM(CASE registro
            WHEN 'entrada' THEN cantidad
            WHEN 'salida' THEN -cantidad
            WHEN 'eliminar' THEN -cantidad
    END) AS `stock_held`
FROM inventario
GROUP BY producto_id;