Get rows with at least two columns without zero

Question

Let's say I have the following table:

CREATE TABLE person ( key integer NOT NULL AI , name varchar NOT NULL DEFAULT 0, column1 integer NOT NULL DEFAULT 0, column2 integer NOT NULL DEFAULT 0, column3 integer NOT NULL DEFAULT 0, column4 integer NOT NULL DEFAULT 0, column5 integer NOT NULL DEFAULT 0, column6 integer NOT NULL DEFAULT 0, CONSTRAINT pk PRIMARY KEY (key) ) 

Now I want to have sql that gets me rows that have at least two non-zero column values ​​for columns column1 and column6.

eg. if we have lines

 1 TEST 0 0 6 1 0 5 2 ABCD 0 0 0 0 0 5 3 DEFG 0 0 4 1 0 5 4 HIJK 0 0 0 1 0 0 

so my query should return ids 1 and 3 since they have at least 2 non-zero columns.

Answer 1

Try the following:

 SELECT * FROM person WHERE (column1 <> 0 + column2 <> 0 + column3 <> 0 + column4 <> 0 + column5 <> 0 + column6 <> 0 ) > 1; 

OR

 SELECT p.* FROM person p INNER JOIN (SELECT A.key FROM (SELECT p.key, p.column1 AS columnData FROM person p WHEN p.column1 <> 0 UNION ALL SELECT p.key, p.column2 AS columnData FROM person p WHEN p.column2 <> 0 UNION ALL SELECT p.key, p.column3 AS columnData FROM person p WHEN p.column3 <> 0 UNION ALL SELECT p.key, p.column4 AS columnData FROM person p WHEN p.column4 <> 0 UNION ALL SELECT p.key, p.column5 AS columnData FROM person p WHEN p.column5 <> 0 UNION ALL SELECT p.key, p.column6 AS columnData FROM person p WHEN p.column6 <> 0 ) AS A GROUP BY A.key HAVING COUNT(*) > 1 ) AS A ON p.key = A.key; 

Answer 2

Count the number of columns 0:

 where case when c1 = 0 then 1 else 0 end + case when c2 = 0 then 1 else 0 end ... >= 2