Conditional sum 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's are options. There can go from 1 to 9, 0 means that question was not answer.

How can i "sum" in a column called "Answered", like so:

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

A value over 0, means answered.

I try with CASE WHEN, IF statements.

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 cumbersome than case. SIGN gives 1 for a value > 0

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

Edit, May 2013. Actually, this can be simpler with

SIGN(OP1+OP2+OP3) AS T

Answer 3

Others have answered your question, but ideally you wouldn't store your data like that.

This table would 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 sum for an ID with a simpler query:

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

It also scales better should you choose to add more options, and provides an easier way to data drive queries about particular option values.