I want to count the number of columns that have a certain value in sql. Example:
A B C D E
1 2 1 2 2
How can I count that there are 3 columns have the value 2.
Add a comment
|
2 Answers
You can use decode for this:
Select decode(a, 2, 1, 0)
+ decode(b, 2, 1, 0)
+ decode(c, 2, 1, 0)
+ decode(d, 2, 1, 0)
+ decode(e, 2, 1, 0)
from my_tab
Alternative using case:
Select (case a when 2 then 1 else 0 end)
+ (case b when 2 then 1 else 0 end)
+ (case c when 2 then 1 else 0 end)
+ (case d when 2 then 1 else 0 end)
+ (case e when 2 then 1 else 0 end)
from my_tab
Comments
Just to take Frank Schmitt's one step further, you can tuck the "value you want to count" away in an inline table to avoid repeating it:
Select
decode(a, countthis.countvalue, 1, 0)
+ decode(b, countthis.countvalue, 1, 0)
+ decode(c, countthis.countvalue, 1, 0)
+ decode(d, countthis.countvalue, 1, 0)
+ decode(e, countthis.countvalue, 1, 0)
from
my_tab
,(select 2 as countvalue from dual) countthis
