unfoudev unfoudev - 6 months ago 8
SQL Question

SQL: Count colums equal to a value

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

fot exemple 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.

Thanks

Answer

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;