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.

JNK · Accepted Answer · 2011-07-21 16:13:15Z

7

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

answered Jul 21, 2011 at 16:13

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gbn · Accepted Answer · 2013-05-02 14:16:08Z

4

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

edited May 2, 2013 at 14:16

answered Jul 21, 2011 at 16:20

gbn

434k84 gold badges602 silver badges690 bronze badges

2 Comments

Matt Over a year ago

Wouldn't SIGN(OP1+OP2+OP3) only give -1, 0, or 1? So it will never give 2 as required by the OP's example. Your original answer seems correct.

gbn Over a year ago

@Matt: it depends how you read "A value over 0, means answered". 0 or 1 may be enough

Peter Alexander · Accepted Answer · 2011-07-21 16:18:01Z

2

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.

answered Jul 21, 2011 at 16:18

Peter Alexander

54.4k14 gold badges121 silver badges169 bronze badges

1 Comment

Esselans Over a year ago

Yep, but this table is not mine, i've to write a SP to add that column and fill it !

Leons · Accepted Answer · 2011-07-21 16:15:11Z

0

how 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:

answered Jul 21, 2011 at 16:15

Leons

2,6741 gold badge23 silver badges26 bronze badges

1 Comment

JNK Over a year ago

Won't work since he wants to count the occurences of values greater than 0, not sum the values themselves.

Rahul · Accepted Answer · 2011-07-21 16:17:43Z

0

Try this:

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

answered Jul 21, 2011 at 16:17

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

2 Comments

Peter Alexander Over a year ago

He doesn't want the sum, he wants the count of the ops that aren't 0.

Rahul Over a year ago

mistake, didn't read the question properly. Your's is the one. Thanks.

Collectives™ on Stack Overflow

Conditional sum in SQL Server

5 Answers 5

Comments

2 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related