SQL SELECT Sum Subquery Returning NULL

Question

I have a column that contains a concatenated list of items. Each of those components represents an id that I have from a different table (tblComponents). I am trying to use each of those items (using WHERE IN within my subquery) to find the qty of each component per item:

 SELECT s.title 'Product SKU'
 ,s.price 'Item Price' 
 ,CASE WHEN (select SUM(c.qty) from tblComponents where CID IN (s.Items)) = 0 
    THEN 1 
    ELSE c.qty 
 END 'Items Qty' 
 FROM tblsku s JOIN tblcomponent c ON c.idtblcomponent = s.idtblcomponent 
 JOIN tblgrade g ON g.idtblgrade = c.idtblgrade 
 WHERE --etc...

I've tried to separate the values within the WHERE IN using:

'''' + REPLACE(s.Items, ',', ''', ''') + ''''

It still returns a null value.

The SUM() is returning a null value.

Sample:

tblSku - Items field contains values 1,2 (in same field)

tblComponent CID 1 contains qty of 2, CID 2 contains qty of 3.

In this scenario I would like to display 5 as the SUM for field 'Items Qty'

tblSku - Items field contains values 1,2 (in same field) does this mean there is 1 record and that record has the value "1,2" in the Items field? If so, then there's your problem: IN doesn't recognize comma-separated string as a list of values; it's just a single string value. — Josh Part
– Josh Part, Commented Nov 6, 2015 at 22:21
Right. I've tried to change it so that 1,2 would read as '1','2' but I'm still getting NULL. — BrettKB
– BrettKB, Commented Nov 6, 2015 at 22:35
@BrettKB Replace doesnt work. Check my answer and see how you split the string into rows — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 6, 2015 at 22:37

Community · Accepted Answer · 2017-05-23 11:44:31Z

1

Just because you have a string '1,2'

Doesnt mean those things are equal.

 CID IN ('1,2') <> CID IN (1,2)

You have to split the string into rows with something like this

Turning a Comma Separated string into individual rows

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Nov 6, 2015 at 22:21

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL SELECT Sum Subquery Returning NULL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related