title: SQL Counting multiple columns for same ID in same table
body: <p>I have a table with columns <code>GameID, GoalID, PlayerID, Assist1ID, Assist2ID</code> (all integers). <code>PlayerID</code> translates as the <code>ID</code> of the person who actually scored the goal, but <code>Assist1ID</code> and <code>Assist2ID</code> also get player IDs.</p>
  
  <p>I am trying to get a dataset with the distinct <code>PlayerID</code>'s (from the <code>PlayerID</code> column or either of the assist columns), a count of goals (<code>PlayerID</code> column) and a count of assists (which is actually the sum of counting columns <code>Assist1</code> and <code>Assist2</code> when that <code>playerID</code> occurs). A <code>PlayerID</code> will never be in more than one of those columns. </p>
  
  <p>I have been trying several approaches, mostly with UNION ALL, as well as some SUM/CASE but I am just not getting it. Should I be using a temporary table for this, or is there a way to check the rows, and if the <code>PlayerID</code>.</p>
  
  <p>Example: (note that <code>GoalID</code> and <code>GameID</code> aren't really important in this case)</p>
  
  <pre><code>GameID | GoalID | PlayerID   | Assist1ID | Assist2ID
    1    |    1   |     1876   |   2098    |     1097 
    1    |    2   |     2098   |   1829    |     1876 
    1    |    3   |     2098   |   1876    |     ---- 
  </code></pre>
  
  <p>My query should return:</p>
  
  <pre><code>PlayerID | Goals   | Assists
   1876    |    1    |    2
   2098    |    2    |    1
   1829    |    0    |    1
   1097    |    0    |    1
  </code></pre>
  
  <p>etc</p>
  
  <p>Is this actually possible, or will I have to do some work in the code part of things?</p>