SQL Question

Subquery returned more than 1 value due to where and group by

I want to make a simple view that allows the to see if the sum of stock from different warehouses are >0 and < that the minimum stock of said product.

Here is the code:

SELECT a.code, a.description, a.stk_min , b.Stk_Qty
FROM a
INNER JOIN b ON a.code = b.productcode
WHERE a.stk_min > 0 and

(SELECT SUM(b.Stk_Qty )
FROM b
INNER JOIN a ON a.code = b.productcode
Group By a.code) <= a.stk_min


And of course I get the error


Subquery returned more than 1 value. This is not permitted when the subquery follows
=
,
!=
,
<
,
<=
,
>
,
>=
or when the subquery is used as an expression.


Is there something I can do to change this?

Answer

You are looking for a correlated subquery, not group by. This query should fix the problem:

SELECT a.code, a.description, a.stk_min , b.Stk_Qty
FROM a JOIN
     b
     ON a.code = b.productcode
WHERE a.stk_min > 0 and 
      (SELECT SUM(b.Stk_Qty )
       FROM b2 
       WHERE a.code = b2.productcode
      ) <= a.stk_min;

I suspect you want this:

SELECT a.code, a.description, a.stk_min, SUM(b.Stk_Qty)
FROM a LEFT JOIN
     b
     ON a.code = b.productcode
WHERE a.stk_min > 0
GROUP BY a.code, a.description, a.stk_min
HAVING COALESCE(SUM(b.Stk_Qty), 0) < a.stk_min;