Can´t make this subquery with SUM work

Question

I have the following code:

SELECT LDP.Id_Lista_Precios,
       LDP.Id_Producto,
       PR.STOCK,
       (SELECT SUM(Cantidad) FROM Pedidos_Detalle PD 
          WHERE PD.Id_Producto=LDP.Id_Producto AND PD.Id_Pedido 
          IN (SELECT Id_Pedido FROM Pedidos WHERE Id_Estado=1 OR Id_Estado=6))
FROM Listas_Precios_Detalle LDP
INNER JOIN Productos PR ON PR.Id_Producto=LDP.Id_Producto
WHERE Id_Lista_Precios=6

Please, focus on this expression:

(SELECT SUM(Cantidad) FROM Pedidos_Detalle PD
WHERE PD.Id_Producto=LDP.Id_Producto AND PD.Id_Pedido
IN (SELECT Id_Pedido FROM Pedidos WHERE Id_Estado=1 OR Id_Estado=6))

Basically, I want this to return some data about the chosen pricelist and the total sum of each product on all "on wait" and "preparing" orders status, only them.

That column just returns NULL, but...if you hardcore the product id like this:

(SELECT SUM(Cantidad) FROM Pedidos_Detalle PD
WHERE PD.Id_Producto=16 AND PD.Id_Pedido
IN (SELECT Id_Pedido FROM Pedidos WHERE Id_Estado=1 OR Id_Estado=6))

It works fine!

Answer 1

Your query looks fine:

SELECT LDP.Id_Lista_Precios, LDP.Id_Producto, PR.STOCK,
       (SELECT SUM(Cantidad)
        FROM Pedidos_Detalle PD
        WHERE PD.Id_Producto = LDP.Id_Producto AND
              PD.Id_Pedido IN (SELECT Id_Pedido FROM Pedidos WHERE Id_Estado IN (1 , 6) )
       )
FROM Listas_Precios_Detalle LDP INNER JOIN
     Productos PR
     ON PR.Id_Producto = LDP.Id_Producto
WHERE Id_Lista_Precios = 6

There is nothing obviously amiss with the correlated subquery.