title: How to write a sql query returning the count of data from a table that meet requirements
body: <p>I have a table with ID and City as columns.  I want to write a sql query that will return ID and count(ID) as columns where City = City and ID = ID.</p>
  
  <p>I have this so far:</p>
  
  <pre><code>select N.ID, count(N.ID) as OpenCities
  into #new2 
  from #new N
  where ...
  </code></pre>
  
  <p>the where is the part I'm having trouble with.  Please help.  Thank you.</p>
  
  <p>Sample Data:</p>
  
  <pre><code>ID   |   City
  =============
  1      New York
  1      Chicago
  2      New Jersey
  2      Chicago
  2      Chicago
  3      Miami
  3      Miami
  3      Miami
  3      Miami
  3      Tuscon
  </code></pre>
  
  <p>What I want returned:</p>
  
  <pre><code>ID   |   OpenCities
  ===================
  2        2
  3        4
  </code></pre>