By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
songID int not null,
artistID int not null,
primary key (songID, artistID)
);
INSERT INTO mytable (songID, artistID) VALUES (
1, 101), (1, 102), (1, 103),
(2, 102), (2, 103),
(3, 101), (3, 104);
WITH ARTISTS AS
(
SELECT DISTINCT artistID
FROM mytable
)
, ARTISTCOMBOS AS
(
SELECT a1.artistID AS artistID1, a2.artistID AS artistID2
FROM ARTISTS a1
JOIN ARTISTS a2 ON a2.artistID > a1.artistID
)
, SONGARTISTCOMBOS AS
(
SELECT t1.artistID AS artistID1, t2.artistID AS artistID2, COUNT(DISTINCT t1.songID) AS TotalSongs
FROM mytable t1
JOIN mytable t2 ON t2.songID = t1.songID AND t2.artistID > t1.artistID
GROUP BY t1.artistID, t2.artistID
)
SELECT
a.artistID1 ||' & '|| a.artistID2 as "artist combinations",
COALESCE(sa.TotalSongs, 0) AS "Count"
FROM ARTISTCOMBOS a
LEFT JOIN SONGARTISTCOMBOS sa
ON sa.artistID1 = a.artistID1 AND sa.artistID2 = a.artistID2
ORDER BY a.artistID1, a.artistID2
| artist combinations | Count |
|---|---|
| 101 & 102 | 1 |
| 101 & 103 | 1 |
| 101 & 104 | 1 |
| 102 & 103 | 2 |
| 102 & 104 | 0 |
| 103 & 104 | 0 |