title: SQL: Query returns count of records with specific value as a column
body: <pre><code>SELECT DISTINCTROW P.ID, First(I.Type) AS [First Of Type], Count(*) AS [Count Of I]
  FROM P INNER JOIN I ON P.[ID] = I.[P ID]
  GROUP BY P.ID;
  </code></pre>
  
  <p>Where the types are "a" and "b". My query will return:</p>
  
  <pre><code>ID         First of Type         Count of I
  1          a                     6
  </code></pre>
  
  <p>This is great but what I'd like is:</p>
  
  <pre><code>ID         First of Type         Count of I        Count of a        Count of b
  1          a                     6                 2                 4
  </code></pre>
  
  <p>I can't figure out how to make this work. Any help would be much appreciated!</p>
  
  <p>EDIT:</p>
  
  <p>I'm doing this in Access. My tables look like:</p>
  
  <pre><code>Table P
  ID           Name
  1            Alice
  2            Bob
  
  
  Table I
  ID           P ID       Type
  1            1          a
  2            1          b
  3            1          a
  4            1          b
  5            1          b
  6            1          b
  7            2          b
  </code></pre>
  
  <p>And I want to return</p>
  
  <pre><code>ID         First of Type         Count of I        Count of a        Count of b
  1          a                     6                 2                 4
  2          b                     1                 0                 1
  </code></pre>
  
  <p>Hope this makes sense. I tried to use the "union all" syntax but no luck with what I'm trying so far.</p>