CODEWITHSUNDEEP
sqloracle-database
Abhijith Gururaj
Abhijith Gururaj

Reputation: 465

Find duplicate values among multiple columns across different rows

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!

Upvotes: 3

Views: 116

Answers (4)

sstan
sstan

Reputation: 36483

Here is another very straight-forward option using Oracle's unpivot keyword:

select id, count(*) as replicates
from tbl
unpivot
(
  id for original_column_name in (id1, id2, id3, id4)  
)
group by id

... and of course, you can add a having and/or order by clause as you please.

Upvotes: 0

jarlh
jarlh

Reputation: 44766

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.

Upvotes: 3

Avi
Avi

Reputation: 1145

select id, sum(id_cnt)
(select ID1 as ID,count(*) as id_cnt from tableA group by ID1
union all
select ID2,count(*)from tableA group by ID2
union all
select ID3,count(*)from tableA group by ID3
union all
select ID4,count(*)from tableA group by ID4)
group by id

Outpout will be similar to 

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

Upvotes: 1

nilsman
nilsman

Reputation: 396

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;

Upvotes: 1

Related Questions