Herak5 Herak5 - 4 months ago 7
SQL Question

Sum of similiar columns for each row

I have table like below

doctor_id forename surname email ...
1 jon doe jon@doe.com ...
2 john dove john@dove.com ...
3 jane dane jane@dane.com ...
4 foo bar foo@bar.com ...
5 bar foo bar@foo.com ...


Please consider that my table has about 10 more columns.

I have set of data (forename = "jon", surname="doe", email="foo@bar.com", etc.)

I want to check each row for number of columns that have same value as columns in set of data

doctor_id forename surname email ... similiarities
1 jon doe jon@doe.com ... 2
2 john dove john@dove.com ... 0
3 jane dane jane@dane.com ... 0
4 foo bar foo@bar.com ... 1
5 bar foo bar@foo.com ... 0


and choose only those rows with number of similiarities greater than 3.

Answer

Does this do what you want?

select t.*,
       ((case when forename = 'jon' then 1 else 0 end) +
        (case when surname = 'doe' then 1 else 0 end) +
        (case when email = 'foo@bar.com' then 1 else 0 end)
       ) as similarities
from t;
Comments