Using Table Column in IN Clause

Transact-SQL
1

I have a table: Othercharges
which consists of columns: code, name, primarykey, groupby, jan

I want to SUM the values of Jan using groupby which is based on the Primarykey

I tried using this code:

 UPDATE othercharges
     SET JAN = (SELECT SUM(jan) FROM othercharges WHERE primarykey IN(groupby) and code = code)
     WHERE Groupby NOT IN('NULL')

but its giving me a NULL result because it reads it as IN('1,2,3') not IN(1,2,3) :

code         name         primarykey        groupby        jan
WDS          Wood             1               NULL         200
FDS          Food             2               NULL         100
IRN          Iron             3               NULL         300
STL          Steel            4               NULL         400
SUM          Sum              5              1,2,3         NULL  <---result

Supposed to output:

code         name         primarykey        groupby        jan
WDS          Wood             1               NULL         200
FDS          Food             2               NULL         100
IRN          Iron             3               NULL         300
STL          Steel            4               NULL         400
SUM          Sum              5              1,2,3         500 <---result
2

You have to unpivot your groupby column, easiest way to do this is into a table. This may help you better understand what you need.

3

I have couple of comments:

  1. If at all possible, redesign your groupby column. When you store multiple values (the comma-separated 1,2,3 in your case), that makes it harder to query as you are finding out. If you want to get technical, doing it that way violates the first normal form.

  2. If you are stuck with this table design, to update the null value in Jan column, you have couple of choices. The quick and inefficient way is as follows:

    UPDATE othercharges SET
    JAN = SUM(JAN)
    WHERE
    ',' + groupby + ',' LIKE '%,' + CAST(primarykey AS VARCHAR(32)) + ',%';

A better approach would be to split the comma-separated string into a virtual table using a string-splitter function (e.g. here ) and then join to that virtual table.

  1. NULL is a special thing. It is not a string. So Groupby NOT IN('NULL') will not work. When you do that you are comparing to the string NULL rather than NULL value. You could have used Groupby NOT IN (NULL), but that will not work either for reasons that I won't bore you with. So the way say something is not equal to null would Groupby IS NOT NULL.
4

Strike that query that I posted earlier. Instead use this:

UPDATE o1 SET
	JAN = SUM(o2.JAN)
FROM
	othercharges o1
	INNER JOIN othercharges o2 ON
		',' + o1.groupby + ',' LIKE '%,' + CAST(o2.primarykey AS VARCHAR(32)) + ',%';