CODEWITHSUNDEEP
sqlpostgresql
daisy_speed_713
daisy_speed_713

Reputation: 43

SQL: Need to select rows where at least 3 columns are not equal to specific value

I need to select rows where at least three columns are not equal to "Unknown"

Example dataset: 

id     condition_A      condition_B      condition_C     condition_D     Condition_E    
1      Unknown          Positive         Negative        Negative        Unknown
2      Unknown          Unknown          Negative        Negative        Unknown
3      Positive         Positive         Negative        Negative        Unknown

In this case I would want the query to return rows 1 and 3.

Upvotes: 0

Views: 467

Answers (1)

forpas
forpas

Reputation: 164194

I think this will do it:

select * from tablename
where 3 <=
  (condition_A <> 'Unknown')::int + 
  (condition_B <> 'Unknown')::int + 
  (condition_C <> 'Unknown')::int + 
  (condition_D <> 'Unknown')::int + 
  (condition_E <> 'Unknown')::int

See the demo.
Results:

| id  | condition_a | condition_b | condition_c | condition_d | condition_e |
| --- | ----------- | ----------- | ----------- | ----------- | ----------- |
| 1   | Unknown     | Positive    | Negative    | Negative    | Unknown     |
| 3   | Positive    | Positive    | Negative    | Negative    | Unknown     |

Upvotes: 2

Related Questions