title: SQL Count Expressions
body: <p>I am trying to create a table to will count the occurrences of each position for various offices.</p>
  
  <p>So if my data is as follows:</p>
  
  <pre><code>Office Position
  A      Manager
  A      Supervisor
  A      Entry Level
  A      Entry Level
  B      Manager
  B      Entry Level
  </code></pre>
  
  <p>I would want my code to return:</p>
  
  <pre><code>Office    Managers    Supervisors     EntryLevel
  A            1             1              2
  B            1             0              1
  </code></pre>
  
  <p>I have my code below. The issue is that this code counts the total amount of occurrences, not the unique count to each office. The results are as follows</p>
  
  <pre><code>A   2   1   3
  B   2   1   3
  
  CREATE TABLE OfficeTest AS
  SELECT DISTINCT Office,
  (Select COUNT(Position) FROM OfficeData  WHERE Make_Name = 'Manager') as Managers, 
  (Select COUNT(Position) FROM OfficeData  WHERE Make_Name = 'Supervisor') as Supervisors,
  (Select COUNT(Position) FROM OfficeData  WHERE Make_Name = 'Entry Level') as EntryLevel
  
  FROM OfficeData 
  GROUP BY Office;
  </code></pre>
  
  <p>Any ideas on how to fix this?</p>