title: SQL: conditional count statement
body: <p>I am new to SQL and I have a SQL database with two columns, which in combination are unique (but their individual values are not): <code>songID</code> and <code>artistID</code>. Each song is recorded by different artists and each artist recorded different songs (see code below). I now want to extract information on who cooperated with whom.</p>
  
  <p>I have tried</p>
  
  <pre><code>SELECT songID, COUNT(songID) FROM mytable
  GROUP BY artist ID;
  </code></pre>
  
  <p>but this doesn't quite get me the results I want.</p>
  
  <p>Consider this sample code:</p>
  
  <pre><code>CREATE TABLE "mytable" ("songID" int, "artistID" int);
  INSERT INTO mytable ('songID', 'artistID') VALUES (1, 101), (1, 102), (1, 103), (2, 102), (2, 103), (3, 101), (3, 104);
  </code></pre>
  
  <p>The desired outcome is obtained logically by looking at song ID, e.g.: <code>songID</code> = 1: <code>artistID</code>s = <code>{101, 102, 103}</code>, so artist <code>101</code> has one common song with artist <code>102</code> and one with artist <code>103</code>, and artist <code>102</code> additionally has one common song with artist <code>103</code>. (Repeat for every <code>songID</code>). The desired result thus looks something like this:</p>
  
  <pre><code>"artist combinations"  "count"
  "101 &amp; 102"              1
  "101 &amp; 103"              1
  "101 &amp; 104"              1
  "102 &amp; 103"              2
  "102 &amp; 104"              0
  "103 &amp; 104"              0
  </code></pre>
  
  <p>Ideally, I would then sort this by <code>count</code>. Could someone point me in the correct direction?</p>