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.

score 7 · Accepted Answer · answered Jul 21 '11 at 16:13

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 '11 at 16:13

JNK

63,321
15
122
138

gbn · Answer 2 · 2013-05-02T14:16:08.333

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 02 '13 at 14:16

answered Jul 21 '11 at 16:20

gbn

422,506
82
585
676

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. – Matt Aug 27 '13 at 18:14
@Matt: it depends how you read "A value over 0, means answered". 0 or 1 may be enough – gbn Aug 27 '13 at 18:46

score 2 · Answer 3 · answered Jul 21 '11 at 16:18

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.

Yep, but this table is not mine, i've to write a SP to add that column and fill it ! — Esselans, Jul 21 '11 at 16:32

score 0 · Answer 4 · answered Jul 21 '11 at 16:15

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 '11 at 16:15

Leons

2,679
1
21
25

Won't work since he wants to count the occurences of values greater than 0, not sum the values themselves. – JNK Jul 21 '11 at 16:16

score 0 · Answer 5 · answered Jul 21 '11 at 16:17

0

Try this:

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

answered Jul 21 '11 at 16:17

Rahul

76,197
13
71
125

He doesn't want the sum, he wants the count of the ops that aren't 0. – Peter Alexander Jul 21 '11 at 16:18
mistake, didn't read the question properly. Your's is the one. Thanks. – Rahul Jul 21 '11 at 16:20

Conditional sum in SQL Server

5 Answers5

Linked