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





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







4
















This question already has an answer here:




  • Count where any 3 columns have values (not null)

    1 answer




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);









share|improve this question















marked as duplicate by Paul White sql-server
Users with the  sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Apr 5 at 4:58


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

























    4
















    This question already has an answer here:




    • Count where any 3 columns have values (not null)

      1 answer




    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);









    share|improve this question















    marked as duplicate by Paul White sql-server
    Users with the  sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.

    StackExchange.ready(function() {
    if (StackExchange.options.isMobile) return;

    $('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
    var $hover = $(this).addClass('hover-bound'),
    $msg = $hover.siblings('.dupe-hammer-message');

    $hover.hover(
    function() {
    $hover.showInfoMessage('', {
    messageElement: $msg.clone().show(),
    transient: false,
    position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
    dismissable: false,
    relativeToBody: true
    });
    },
    function() {
    StackExchange.helpers.removeMessages();
    }
    );
    });
    });
    Apr 5 at 4:58


    This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.





















      4












      4








      4


      0







      This question already has an answer here:




      • Count where any 3 columns have values (not null)

        1 answer




      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);









      share|improve this question

















      This question already has an answer here:




      • Count where any 3 columns have values (not null)

        1 answer




      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);




      This question already has an answer here:




      • Count where any 3 columns have values (not null)

        1 answer








      sql-server query isnull






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Apr 3 at 19:23









      MDCCL

      6,86331745




      6,86331745










      asked Apr 3 at 16:57









      sqlNewiesqlNewie

      283




      283




      marked as duplicate by Paul White sql-server
      Users with the  sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.

      StackExchange.ready(function() {
      if (StackExchange.options.isMobile) return;

      $('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
      var $hover = $(this).addClass('hover-bound'),
      $msg = $hover.siblings('.dupe-hammer-message');

      $hover.hover(
      function() {
      $hover.showInfoMessage('', {
      messageElement: $msg.clone().show(),
      transient: false,
      position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
      dismissable: false,
      relativeToBody: true
      });
      },
      function() {
      StackExchange.helpers.removeMessages();
      }
      );
      });
      });
      Apr 5 at 4:58


      This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.









      marked as duplicate by Paul White sql-server
      Users with the  sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.

      StackExchange.ready(function() {
      if (StackExchange.options.isMobile) return;

      $('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
      var $hover = $(this).addClass('hover-bound'),
      $msg = $hover.siblings('.dupe-hammer-message');

      $hover.hover(
      function() {
      $hover.showInfoMessage('', {
      messageElement: $msg.clone().show(),
      transient: false,
      position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
      dismissable: false,
      relativeToBody: true
      });
      },
      function() {
      StackExchange.helpers.removeMessages();
      }
      );
      });
      });
      Apr 5 at 4:58


      This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
























          2 Answers
          2






          active

          oldest

          votes


















          7














          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.






          share|improve this answer































            8














            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;





            share|improve this answer






























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              7














              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.






              share|improve this answer




























                7














                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.






                share|improve this answer


























                  7












                  7








                  7







                  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.






                  share|improve this answer













                  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.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Apr 3 at 17:07









                  Josh DarnellJosh Darnell

                  8,05522243




                  8,05522243

























                      8














                      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;





                      share|improve this answer




























                        8














                        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;





                        share|improve this answer


























                          8












                          8








                          8







                          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;





                          share|improve this answer













                          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;






                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Apr 3 at 17:09









                          Aaron BertrandAaron Bertrand

                          154k18298493




                          154k18298493















                              Popular posts from this blog

                              Plaza Victoria

                              Puebla de Zaragoza

                              Musa