Problem solve
Get help with specific problems with your technologies, process and projects.
Counting matches in a self-join
I just want to take the phonenumber count matches with other tapeids.
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.
