Counting duplicates within combinations of groups

[James2020]

Hi All,

I have a database table which contains approx 20,000 records in the following format:

Disk Track
=====================
CD1 Unique to CD1
CD1 On all three
CD1 On CD1 and CD2
CD1 On CD1 and CD3

CD2 Unique to CD2
CD2 On all three
CD2 On CD1 and CD2
CD2 On CD2 and CD3
CD2 Another on CD2 and CD3

CD3 Unique to CD3
CD3 On all three
CD3 On CD1 and CD3
CD3 On CD2 and CD3
CD3 Another on CD2 and CD3


Is it possible to create an SQL statement to list all combinations of two disks, for example:

DiskA DiskB
===========
CD1 CD2
CD1 CD3
CD2 CD3


Is it possible to create an SQL statement to list "counts of duplicates" WITHIN (or between) each combination of two disks, for example:

DiskA DiskB CountOfDuplicatedTracks
================================
CD1 CD2 2
CD1 CD3 2
CD2 CD3 3


Thank you for your help,
Best regards,
James

P.S. This is a 'real question', with a real table, and not just a 'homework question'

 

[r937]

does this satisfy your first question?

select diskA, diskB
  from ( select distinct disk as diskA
           from daTable ) as A
cross
  join ( select distinct disk as diskB
           from daTable ) as B
 where diskA < diskB

r937.com | rudy.ca

 

[James2020]

Thanks r937,

I have managed to solve part 2 of the problem. Just in case anyone else is interested, my solution to part 2 uses two SQL statements...

Make_B:
SELECT CD AS CD_B, Track AS Track_B FROM MyTable;

Joiner:
SELECT CD, CD_B, Count(Track) AS CountOfDuplicates
FROM MyTable INNER JOIN Make_B ON MyTable.Track=Make_B.Track_B
GROUP BY CD, CD_B HAVING CD_B>CD;

Regards,
James