3

I'm pretty new to MySQL (used to programming in R/Stata), and getting stuck on this - I'm probably going about it the wrong way. Any pointers would be appreciated!

I have a large database (a couple million rows) and want to select records where at least two fields from a selection of variables are non-missing (not NULL). I can find lots on counting missing values by column, but not by row.

As an example, I would like to write a select statement for the table below, based only on var1-var5, that would select ID 1 and 3 as they have at least 2 non-NULL values.

ID | var1 | var2 | var3 | var4  | var5 | var6 | var7
1  | NULL | 4    | X    | NULL  | NULL | NULL | R69
2  | NULL | NULL | NULL | NULL  | 1    | X    | J10
3  | 4    | 5    | 23   | 1jan03| 2    | 22   | Z38

I was thinking creating a user-defined variable to count the number of non-missing values and delete rows where this is <2, but it doesn't seem to work.

SET @include = 0;
SELECT var1, var2, var3, var4, var5 FROM db1;
@include := @include + 1 if var1 is not null;
DELETE FROM db1 WHERE @include<2;

I'm guessing this is something I can do with a WHERE statement in the SELECT line, but I can only think of writing it so it only selects rows where everything is non-missing, not at least 2 values.

  • 1
    Do you want just to select those rows? Or also delete the rows that have less than 2 non-null values? or both? – ypercubeᵀᴹ Jun 7 '16 at 17:51
  • I would like to select those rows. However, I'm loading the data into a new 'decent quality records' table, so if it would be easier to delete the rows, I could delete them from the new table. – LindaW Jun 8 '16 at 16:11
1

To get only the rows that have at least 2 non-null values in the 5 columns:

SELECT *
FROM db1
WHERE (var1 IS NOT NULL) + (var2 IS NOT NULL) 
      + (var3 IS NOT NULL) + (var4 IS NOT NULL) 
      + (var5 IS NOT NULL) >= 2 ;

To delete the rows that have less than 2 non-null values, we can just use the reverse condition:

DELETE FROM db1
WHERE (var1 IS NOT NULL) + (var2 IS NOT NULL) 
      + (var3 IS NOT NULL) + (var4 IS NOT NULL) 
      + (var5 IS NOT NULL) < 2 ;

The (varX IS NOT NULL) is just a shorter form of the standard but more complicated:

 CASE WHEN varX IS NOT NULL THEN 1 ELSE 0 END 

Works because MySQL translates TRUE to 1 and FALSE to 0.

2

I have the following way to get the IDs of the rows that have at least two not null values; however, I am afraid this is not efficient when it comes to few million rows per table. I'd like to share it anyway:

mysql> select * from l;
+------+------+------+------+------+------+------+------+
| id   | var1 | var2 | var3 | var4 | var5 | var6 | var7 |
+------+------+------+------+------+------+------+------+
|    1 | NULL |    4 |    9 | NULL | NULL | NULL |    7 |
|    2 | NULL | NULL | NULL | NULL | NULL |    1 |    2 |
|    3 |    4 |    5 |   23 |    1 |    2 |    4 |   22 |
+------+------+------+------+------+------+------+------+
3 rows in set (0.00 sec)

mysql> select id, 5-(isnull(var1)+isnull(var2)+isnull(var3)+isnull(var4)+isnull(var5)) AS non_null_count from l;
+------+----------------+
| id   | non_null_count |
+------+----------------+
|    1 |              2 |
|    2 |              0 |
|    3 |              5 |
+------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT id FROM (select id, 5-(isnull(var1)+isnull(var2)+isnull(var3)+isnull(var4)+isnull(var5)) AS non_null_count from l)x WHERE non_null_count>=2;
+------+
| id   |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.01 sec)

Basically, I am "counting" how many non-null values exist in each row.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.