Conditional Amount in SQL Server

Question

I have this table in MSSQL:

ID OP1 OP2 OP3 330 0 0 1 331 1 0 0 332 3 2 0 

OP are options. It can go from 1 to 9, 0 means that the question was not the answer.

How can I “summarize” in the column “Answer”, for example:

 ID OP1 OP2 OP3 T 330 0 0 1 1 331 1 0 0 1 332 3 2 0 2 

A value greater than 0 means the answer.

I am trying to use CASE WHEN, IF expressions.

Answer 1

Use CASE :

 SELECT Id, OP1, OP2, OP3, (CASE WHEN OP1 > 0 THEN 1 ELSE 0 END + CASE WHEN OP2 > 0 THEN 1 ELSE 0 END + CASE WHEN Op3 > 0 THEN 1 ELSE 0 END) AS T FROM MyTable 

Answer 2

Less bulky than the case. SIGN gives 1 for value> 0

 SELECT ID, OP1,OP2,OP3, SIGN(OP1) + SIGN(OP2) + SIGN(OP3) AS T FROM OPTABLE 

Change, May 2013. This is actually easier with

 SIGN(OP1+OP2+OP3) AS T 

Answer 3

Others answered your question, but ideally you would not have saved your data.

This table will be better:

 id op value 330 1 0 330 2 0 330 3 1 331 1 1 331 2 0 331 3 0 332 1 3 332 2 2 332 3 0 

Then you can get the amount for the ID with a simpler request:

 SELECT COUNT(*) AS T FROM table WHERE id = x AND VALUE > 0 

It also scales better if you decide to add additional parameters and provide an easier way to query data about specific parameter values.

Answer 4

what about the following:

 SELECT ID, OP1, OP2, OP3, CASE WHEN OP1 = 0 THEN 0 ELSE 1 END + CASE WHEN OP2 = 0 THEN 0 ELSE 1 END + CASE WHEN OP3 = 0 THEN 0 ELSE 1 END AS T 

EDIT:

Answer 5

Try the following:

 SELECT ID,OP1,OP2,OP3,(OP1+OP2+OP3) AS T FROM OPTABLE