SQL: select 3 values and count how many times they are different

Say that you need to query some data and that there are three fields like the following (this is part of a larger query): Field1, Field2, Field3.

So you select them like this:

SELECT Field1=MyTable.Field1, Field2=MyTable.Field2, Field3=MyTable.Field3
FROM MyTable

I need to compare these values and return the variable Result that is computed as follows:

• 0 if they are all the same
• 1/2 if two are the same and one is different
• 1 if they are all different.

How should I restructure my query? I think I need a subquery but I am not sure how to structure it.

Answer

You can use case:

select (case when field1 = field2 and field1 = field3 then 0
             when field1 in (field2, field3) or field2 = field3 then 0.5
             else 1
        end) as result