SQL (Redshift): Find if a specific value occurs more than once in multiple columns

user_4671137 0
Viewed 69

Let's say I have 5 columns, which could all contain the same value. I want to calculate a new column that tells me whether a specific value occurs more than once. Example of desired outputs with different situations:

I want to scan all rows that contains at least one of value 'X':

id A B C D E Result
1 X Y X Z True
2 X Y Y Z False
3 Y Y Z False
4 X X Y X True

A 'case when' would in theory be possible, but going through all the options is not doable: this requires too many combinations. Maybe some inner query?

Edit:

I actually found a solution, by making a join. But the answer of Gordon Linoff is much more clean.

select id,
case when b.num_X > 1 then True else False end as result 
from foo f
join (
    select a+b+c+d+e as num_X from (
        select 
        id,
        case when A = 'X' then 1 else 0
        end as a,
        case when B = 'X' then 1 else 0
        end as b,
        case when C = 'X' then 1 else 0
        end as c,
        case when D = 'X' then 1 else 0
        end as d,
        case when E = 'X' then 1 else 0
        end as e
        from foo
    )
) b on f.id = b.id
amazon-redshift sql
1 Answers

One method is to just count them:

select t.*,
       ( (a = 'X')::int + (b = 'X')::int + (c = 'X')::int + (d = 'X')::int + (e = 'X')::int) ) >= 2 as result
from t;

If the columns can contain NULL values, then you need to pay attention to that. One method is to use coalesce() in the above expression:

       ( (coalesce(a, '') = 'X')::int +
         (coalesce(b, '') = 'X')::int +
         (coalesce(c, '') = 'X')::int +
         (coalesce(d, '') = 'X')::int +
         (coalesce(e, '') = 'X')::int)
       ) >= 2 as result
user_11440350
Related