Count occurrences across columns in SQL Server

Question

I want to update a column based on the occurrences of values in multiple columns.

SQL Server 2017 schema setup:

CREATE TABLE Table1 (Part varchar(10),Jan int,Feb int,Mar int,Apr int,Occurrences int)

INSERT INTO Table1 (Part,Jan,Feb,Mar,Apr,Occurrences)VALUES('AAA',null,2,null,1,null),
                                                ('BBB',2,3,5,7,null),
                                                ('CCC',3,null,null,null,null),
                                                ('DDD',4,7,1,null,null)

I want to update the Occurrences column based on the values existing in Jan,Feb,Mar,Apr columns. It should skip the null occurrences and count only if value exists.

For the above schema the occurrences column should be updated as

How can I achieve this?

score 1 · Accepted Answer · edited Dec 05 '19 at 07:21

1

Try this:

Update Table1 set
    Occurrences = ISNUMERIC(jan) + ISNUMERIC(feb) + ISNUMERIC(mar) + ISNUMERIC(apr)

edited Dec 05 '19 at 07:21

Dale K

25,246
15
42
71

answered Dec 05 '19 at 07:02

Red Devil

2,343
2
21
41

@ red devil. How can I make the query dynamic so that the month name jan,feb,mar,etc. is not specified in the update statement? Can you please help? – vicky Dec 05 '19 at 08:45

score 0 · Answer 2 · answered Dec 05 '19 at 07:09

0

Maximize the use of iif() function.

update table1 set Occurrences = iif(coalesce(Jan, 0) != 0, 1, 0) 
+  iif(coalesce(Feb, 0) != 0, 1, 0) 
+  iif(coalesce(Mar, 0) != 0, 1, 0) 
+  iif(coalesce(Apr, 0) != 0, 1, 0);

see dbfiddle.

answered Dec 05 '19 at 07:09

Ed Bangga

12,879
4
16
30

score 0 · Answer 3 · answered Dec 05 '19 at 07:14

You could use unpivot to do that, but additionally you need to pass number of columns you have (4 - ... in query):

SELECT Part, 4 - COUNT(*) FROM (
    SELECT *
    FROM @Table1
    UNPIVOT (MonthVal FOR [Month] IN (Jan, Feb, Mar, Apr)) AS unpvt
) a GROUP BY Part

Count occurrences across columns in SQL Server

3 Answers3