Get Rows With At Least Two Non Zero Columns

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 a sql which get me rows which have atleast two columns values non zero for columns column1 to column6.

e.g. if we have rows

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 ID 1 and 3 as they have atleast 2 non zero columns.

Answer 1

Try this:

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;