title: JOIN on another table after GROUP BY and COUNT
body: <p>I'm trying to make sense of the right way to use <code>JOIN</code>, <code>COUNT(*)</code>, and <code>GROUP BY</code> to do a pretty simple query.  I've actually gotten it to work (see below) but from what I've read, I'm using an extra <code>GROUP BY</code> that I shouldn't be.</p>
  
  <p>(Note: The problem below isn't my actual problem (which deals with more complicated tables), but I've tried to come up with an analogous problem)</p>
  
  <p>I have two tables:</p>
  
  <pre><code>Table: Person
  -------------
  key  name     cityKey
  1    Alice    1
  2    Bob      2
  3    Charles  2
  4    David    1
  
  Table: City
  -------------
  key  name
  1    Albany
  2    Berkeley
  3    Chico
  </code></pre>
  
  <p>I'd like to do a query on the People (with some <code>WHERE</code> clause) that returns </p>
  
  <ul>
  <li>the number of matching people in each city</li>
  <li>the key for the city</li>
  <li>the name of the city.</li>
  </ul>
  
  <p>If I do</p>
  
  <pre><code>SELECT COUNT(Person.key) AS count, City.key AS cityKey, City.name AS cityName
  FROM Person 
  LEFT JOIN City ON Person.cityKey = City.key 
  GROUP BY Person.cityKey, City.name
  </code></pre>
  
  <p>I get the result that I want</p>
  
  <pre><code>count   cityKey   cityName
  2       1         Albany
  2       2         Berkeley
  </code></pre>
  
  <p><em>However</em>, I've <a href="http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/20/60261.aspx" rel="noreferrer">read</a> that throwing in that last part of the <code>GROUP BY</code> clause (<code>City.name</code>) just to make it work is wrong.  </p>
  
  <p>So what's the right way to do this?  I've been trying to google for an answer, but I feel like there's something fundamental that I'm just not getting.</p>