SQL: Count columns equal to a value

Question

I have a table and I would like to write query that shows lines which have all columns to '0' but one .

T_CELKO_TEN_IN_ON_TIO(TIO_ID,TIO_1,TIO_2,TIO_3,TIO_4,TIO_5,TIO_6,TIO_7,TIO_8,TIO_9,TIO_10);

I have numbers in it

for example if I have:

1(id)        0        1        1        0        0        0        0        0        0        0
2(id)        0        0        0        0        0        0        0        1        0        0
3(id)        0        1        -2       3        -4       5        -6       7        -8       5

So the query should prints:

2(id)        0        0        0        0        0        0        0        1        0        0

I have wrote this query:

Select * from T_CELKO_TEN_IN_ON_TIO where SUM  (CASE WHEN  TIO_1='0' THEN 1 ELSE 0 END OR
                                               TIO_2='0' THEN 1 ELSE 0 END OR
                                               TIO_3='0' THEN 1 ELSE 0 END OR
                                               TIO_4='0' THEN 1 ELSE 0 END OR
                                               TIO_5='0' THEN 1 ELSE 0 END OR
                                               TIO_6='0' THEN 1 ELSE 0 END OR
                                               TIO_7='0' THEN 1 ELSE 0 END OR
                                               TIO_8='0' THEN 1 ELSE 0 END OR
                                               TIO_9='0' THEN 1 ELSE 0 END OR 
                                               TIO_10='0' THEN 1 ELSE 0 END)=9;

I get an error: An expression of non-boolean type specified in a context where a condition is expected, near 'OR' but I think even the my query does not work.

Possible duplicate of [How do I use T-SQL's Case/When?](http://stackoverflow.com/questions/3342319/how-do-i-use-t-sqls-case-when) — TZHX, May 27 '16 at 09:43

Shnugo · Accepted Answer · 2016-05-27T09:57:42.883

I hope I understand this correctly:

SUM is an aggregate function and cannot be used in this context. In my code I test each value (assuming they are numeric) if they are 1, zero or other. All other values are returned as 1000. So the pure summation of these values should only be "1" if there are many "0" and only one single "1" value...

Select * from T_CELKO_TEN_IN_ON_TIO 
where ( CASE TIO_1 WHEN 1 THEN 1 WHEN 0 THEN 0 ELSE 1000 END
      + CASE TIO_2 WHEN 1 THEN 1 WHEN 0 THEN 0 ELSE 1000 END
      + CASE TIO_3 WHEN 1 THEN 1 WHEN 0 THEN 0 ELSE 1000 END
      + CASE TIO_4 WHEN 1 THEN 1 WHEN 0 THEN 0 ELSE 1000 END
      + CASE TIO_5 WHEN 1 THEN 1 WHEN 0 THEN 0 ELSE 1000 END
      + CASE TIO_6 WHEN 1 THEN 1 WHEN 0 THEN 0 ELSE 1000 END
      + CASE TIO_7 WHEN 1 THEN 1 WHEN 0 THEN 0 ELSE 1000 END
      + CASE TIO_8 WHEN 1 THEN 1 WHEN 0 THEN 0 ELSE 1000 END
      + CASE TIO_9 WHEN 1 THEN 1 WHEN 0 THEN 0 ELSE 1000 END
      + CASE TIO_10 WHEN 1 THEN 1 WHEN 0 THEN 0 ELSE 1000 END)=1;

UPDATE

I got this wrong, as I thought you want to handle the "1" separately. This should be what you really needed:

Select * from T_CELKO_TEN_IN_ON_TIO 
where ( CASE TIO_1 WHEN 0 THEN 1 ELSE 0 END
      + CASE TIO_2 WHEN 0 THEN 1 ELSE 0 END
      + CASE TIO_3 WHEN 0 THEN 1 ELSE 0 END
      + CASE TIO_4 WHEN 0 THEN 1 ELSE 0 END
      + CASE TIO_5 WHEN 0 THEN 1 ELSE 0 END
      + CASE TIO_6 WHEN 0 THEN 1 ELSE 0 END
      + CASE TIO_7 WHEN 0 THEN 1 ELSE 0 END
      + CASE TIO_8 WHEN 0 THEN 1 ELSE 0 END
      + CASE TIO_9 WHEN 0 THEN 1 ELSE 0 END
      + CASE TIO_10 WHEN 0 THEN 1 ELSE 0 END)=9;

I have changed the inner equals 1 to 0 and the fiinal sum to equal 9, that gives me what I want thanks — unfoudev, May 27 '16 at 09:56
@unfoudev, yes, I just realised this and posted an update... Glad that you could solve your issue! — Shnugo, May 27 '16 at 09:58

score 1 · Answer 2 · answered May 27 '16 at 09:49

May be You want something like this.

Select * from T_CELKO_TEN_IN_ON_TIO 
   where SUM (CASE WHEN  TIO_1='0' THEN 1 
                   WHEN TIO_2='0' THEN 1
                   WHEN TIO_3='0' THEN 1
                   WHEN TIO_4='0' THEN 1
                   WHEN TIO_5='0' THEN 1
                   WHEN TIO_6='0' THEN 1
                   WHEN TIO_7='0' THEN 1
                   WHEN TIO_8='0' THEN 1
                   WHEN TIO_9='0' THEN 1
                   WHEN TIO_10='0' THEN 1 ELSE 0 END) = 9;

SQL: Count columns equal to a value

2 Answers2

UPDATE