CODEWITHSUNDEEP
sql
Dee
Dee

Reputation: 33

How do I select only rows with at least 2 non-missing values

How do I write a query so that I get the numbers of rows where there are at least 2 non null values in the columns for each ID? In the example below, there would be 2 rows with 2 or more non null values.

enter image description here

I'm getting an error Incorrect syntax near '>' with the below query:

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

Upvotes: 0

Views: 379

Answers (1)

forpas
forpas

Reputation: 164099

Your code should work for MySql, so I guess it's not the database that yo use.
Using standard SQL you can do it with CASE expressions:

SELECT *
FROM db1
WHERE 
  CASE WHEN (var1 IS NOT NULL) THEN 1 ELSE 0 END + 
  CASE WHEN (var2 IS NOT NULL) THEN 1 ELSE 0 END + 
  CASE WHEN (var3 IS NOT NULL) THEN 1 ELSE 0 END + 
  CASE WHEN (var4 IS NOT NULL) THEN 1 ELSE 0 END >= 2 ;

Upvotes: 1

Related Questions