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

Question

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?

score 0 · Accepted Answer · answered Sep 27 '16 at 16:36

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;

Worked like a charm :) Thank you soo much! Now lets read up on Having and Coalesce so i can figure out what this does :P — Hélder Filipe Correia Carneiro, Sep 27 '16 at 16:52

score 0 · Answer 2 · answered Sep 27 '16 at 16:47

0

What about

SELECT a.code, a.description, a.stk_min, SUM(b.Stk_Qty) SumQty
FROM a join b on b.productcode = a.code 
group by a.code, a.description, a.stk_min
Where a.stk_min > 0
Having   SUM(b.Stk_Qty) <= a.stk_min

answered Sep 27 '16 at 16:47

Charles Bretana

143,358
22
150
216

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

2 Answers2