title: SQL SELECT id and count of items in same table
body: <p>I have the following SQL table columns...</p>
  
  <pre><code>id | item | position | set
  ---------------------------    
   1 |   1  |     1    | 1
   2 |   1  |     1    | 2
   3 |   2  |     2    | 1
   4 |   3  |     2    | 2
  </code></pre>
  
  <p>In a <em>single</em> query I need to get all the ids of rows that match <code>set='1'</code> while simultaneously counting how many instances in the same table that it's <code>item</code> number is referenced regardless of the <code>set</code>.</p>
  
  <p>Here is what I've been tinkering with so far...</p>
  
  <pre><code>SELECT 
      j1.item, 
      (SELECT count(j1.item) FROM table_join AS j2) AS count 
  FROM 
      table_join AS j1 
  WHERE 
      j1.set = '1';
  </code></pre>
  
  <p>...though the subquery is returning multiple rows. With the above data the first item should have a count of 2, all the other items should have a count of 1.</p>