Extract rows of a table, that include less than x NULLs [duplicate]

Question

I am working with a SQL Server database, which includes a lot of NULLs. To analyse my data, I want to extract all rows of the database table, that include less than x NULL marks (e.g. x=2).

My database is similar to this structure:

 c1        c2      c3      c4        c5        
-----------------------------------------------------
2          3       NULL    1         2
2          NULL    NULL    1         2
2          3       NULL    NULL      2
NULL       3       NULL    1         NULL
2          3       NULL    1         2

I tried the query, which doesn't return an error, but no rows are selected:

SELECT * FROM test123 
WHERE ((ISNULL(c1,1) + ISNULL(c2,1) + ISNULL(c3,1) + ISNULL(c4,1) + ISNULL(c5,1)) < 2);

I expect this query to return the 1st and the fifth row, but the result contains 0 rows.

---

I can't test the following code, because I don't have the rights to write on the database, but here is a (pseudo-) code for creating a table like mine:

CREATE TABLE test123(
    c1 float,
    c2 float,
    c3 float,
    c4 float,
    c5 float
) GO
INSERT test123(c1,c2,c3,c4,c5)
VALUES (2,3,NULL,1,2),
       (2,NULL,NULL,1,2),
       (2,3,NULL,NULL,2),
       (NULL,3,NULL,1,NULL),
       (2,3,NULL,1,2);

Answer 1

You should use a case statement like this:

SELECT * 
FROM test123 
WHERE (
    (CASE WHEN C1 IS NULL THEN 1 ELSE 0 END + 
     CASE WHEN C2 IS NULL THEN 1 ELSE 0 END + 
     CASE WHEN C3 IS NULL THEN 1 ELSE 0 END + 
     CASE WHEN C4 IS NULL THEN 1 ELSE 0 END + 
     CASE WHEN C5 IS NULL THEN 1 ELSE 0 END) 
    < 2);

The ISNULL approach is returning your actual values when the value isn't NULL, which pushes all of the rows over the 2 mark.

Answer 2

Permissions to create a table in the current database shouldn't preclude you from creating one you can work with. You can just create a #temp table:

CREATE TABLE #test123(
    c1 float,
    c2 float,
    c3 float,
    c4 float,
    c5 float
);

INSERT #test123(c1,c2,c3,c4,c5);
VALUES (2,3,NULL,1,2),
       (2,NULL,NULL,1,2),
       (2,3,NULL,NULL,2),
       (NULL,3,NULL,1,NULL),
       (2,3,NULL,1,2);

To see why ISNULL isn't effective here, run this query:

SELECT ISNULL(c1,1), ISNULL(c2,1), ISNULL(c3,1), ISNULL(c4,1), ISNULL(c5,1)
  FROM #test123;

You've given every column in every row a value. So now you're evaluating the SUM of inflated values, and erroneously evaluating a property of the actual value (what happens when one of the values is negative?), instead of evaluating the COUNT of values that either are NULL or are NOT NULL.

It's more code but a simple way to address this is:

SELECT * FROM #test123
  WHERE CASE WHEN c1 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c2 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c3 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c4 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c5 IS NULL THEN 1 ELSE 0 END < 2;