SQL mathematical calculations based on text column

Question

I have a table with 3 text columns on which I want to do some mathematical calculations. Table looks like below:

Date        Column1    Column2    Column3
-----------------------------------------
2012-08-01  STABLE     NEG        STABLE
2012-08-02  NEG        NEG        STABLE
2012-08-03  STABLE     STABLE     STABLE

Want I want to achieve is

• If 2/3 columns is equal to 'STABLE' then it returns 66% i.e. 2/3, as it is in the first row
• If 1/3 columns is equal to 'STABLE' then it returns 33% i.e. 1/3, as it is in the second row
• If 3/3 columns is equal to 'STABLE' then it returns 100% i.e. 3/3, as it is in the third row

I want to know how I can achieve this using SQL? I'm currently working on MSSQL Server 2008 R2.

I hope my question is clear enough.

Answer 1

If those are the only choices for column values:

select ( Len( Column1 ) + Len( Column2 ) + Len( Column3 ) - 9 ) / 3 / 3.0 * 100.0 as 'Percent'
  from Foo

The optimizer should handle the constant folding. They are shown for clarity.

Divine Comedy describes a place for people who write code like this.

Answer 2

So, something like this?

select 
   ((case when col1 = 'Stable' then 1.0 else 0.0 end) + 
   (case when col2 = 'Stable' then 1.0 else 0.0 end) + 
   (case when col3 = 'Stable' then 1.0 else 0.0 end)) / 3.0
from yourtable

You can do some formatting on the output, but should be very close to what your looking for.