I just want to take the phonenumber count matches with other tapeids:
tapeid phonenumber 201 2015550007 201 2015550008 201 2015550009 202 2015550007 202 2015550008 203 2015550006 203 2015550002 203 2015550003 203 2015550007 204 2015550006 204 2015550009
I need the report from the above details as:
tapeid matchingid matches 201 202 2 201 203 1 202 201 1 203 201 1 203 204 1 204 201 1 204 203 1
Thanks for your understanding.
The first thing to notice is that your sample results do not match your sample data. Second is that you're apparently counting the matches twice.
Now, counting the matches twice is actually not a bad idea, since that makes it much easier to look up any given tapeid to find out its matches. So here's the query to do that:
select a.tapeid , b.tapeid as matchingid , count(*) as matches from yourtable as a inner join yourtable as b on a.phonenumber = b.phonenumber and a.tapeid <> b.tapeid group by a.tapeid , b.tapeid order by a.tapeid , b.tapeid
The reason for and a.tapeid <> b.tapeid is to exclude counts for tapeids that match themselves (since they all will). If you wanted to count the matches only once, change it to and a.tapeid < b.tapeid.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading