-2

I have a question about Vertica DB's query that I want to run. I have a table with 5 columns, and each column can get values of 1 or 0, I want to return all the Id of which I have combination of '1' in more than one column. I added a pic for demonstration and I want to return all the ID of the marked rows, regards

p.s. the value of the table can be 1/0/2, what is not allowed are that in the same row to get '1' in two rules.

enter image description here

Barmar
  • 741,623
  • 53
  • 500
  • 612
Bastian
  • 1,089
  • 7
  • 25
  • 74
  • What have you tried to achieve your wanted results? What has your research concerning your problem shown? Can you provide code of your tries? [How do I ask a good question](//stackoverflow.com/help/how-to-ask), [How much research effort is expected](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users), and [How to create a Minimal, Complete, and Verifiable example](//stackoverflow.com/help/mcve) might be helpful to improve your question. – peacetype Jan 14 '18 at 07:59
  • I want to get all the ID's that have more than 1 times the number '1' inside them – Bastian Jan 14 '18 at 08:05
  • Your question says two different things. First it says that the columns can have values of 1 or 0, then it says they can be 1/0/2. Please be consistent. – Barmar Jan 14 '18 at 08:33

1 Answers1

1

If you use a boolean value as a number, it will have the value 1 when true, 0 when false. So you can add up the comparisons:

SELECT id
FROM yourTable
WHERE (rule_1 = 1) + (rule_2 = 1) + (rule_3 = 1) + (rule_4 = 1) > 1

DEMO

In another database you can use CASE expressions to translate conditions to numbers:

SELECT id
FROM yourTable
WHERE (CASE WHEN rule_1 = 1 THEN 1 ELSE 0 END) 
    + (CASE WHEN rule_2 = 1 THEN 1 ELSE 0 END) 
    + (CASE WHEN rule_3 = 1 THEN 1 ELSE 0 END) 
    + (CASE WHEN rule_4 = 1 THEN 1 ELSE 0 END) > 1
Barmar
  • 741,623
  • 53
  • 500
  • 612