Find duplicate values among multiple columns across different rows

Question

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

ID1     ID2     ID3      ID4
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!

Answer 1

select c, count(*)
from
(
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.

Answer 2

SELECT ID, COUNT(*) FROM(
SELECT ID1 ID FROM TBL UNION ALL
SELECT ID2 ID FROM TBL UNION ALL
SELECT ID3 ID FROM TBL UNION ALL
SELECT ID4 ID FROM TBL ) Q
GROUP BY Q.ID;