Abhijith Gururaj Abhijith Gururaj - 3 months ago 8
SQL Question

Find duplicate values among multiple columns across different rows

I have a dataset with multiple columns that look similar to this:

Blue Grey Fuchsia Green
Black Blue Orange Blue
Green Green Yellow Pink
Pink Yellow NA Orange

What I want to do is count how many times each value is duplicated across the four columns. For example, this is what I'd like to get back from the above:

ID Replicates
Blue 3
Black 1
Green 3
Pink 2
Grey 1
Yellow 2
Fuchsia 1
Orange 2

I'd also like to be able to ask which ID value is present in the data set at frequency >2. So the expected result would be: Green and Blue.

Any thoughts on how to do this in Oracle? Thanks!

select c, count(*)
select ID1 as c from tablename
union all
select ID2 as c from tablename
union all
select ID3 as c from tablename
union all
select ID4 as c from tablename
group by c

Add HAVING count(*) > 2 at the end to get only Green and Blue.