title: Efficiently Include Column not in Group By of SQL Query
body: <p>Given</p>
  
  <p><strong>Table A</strong></p>
  
  <pre><code>Id   INTEGER
  Name VARCHAR(50)
  </code></pre>
  
  <p><strong>Table B</strong></p>
  
  <pre><code>Id   INTEGER
  FkId INTEGER  ; Foreign key to Table A
  </code></pre>
  
  <p>I wish to count the occurrances of each <code>FkId</code> value:</p>
  
  <pre><code>SELECT FkId, COUNT(FkId) 
  FROM B 
  GROUP BY FkId
  </code></pre>
  
  <p>Now I simply want to also output the Name from <code>Table A</code>.</p>
  
  <p>This will not work:</p>
  
  <pre><code>SELECT FkId, COUNT(FkId), a.Name
  FROM B b
  INNER JOIN A a ON a.Id=b.FkId
  GROUP BY FkId
  </code></pre>
  
  <p>because <code>a.Name</code> is not contained in the <code>GROUP BY</code> clause (produces <code>is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause</code> error).</p>
  
  <p>The point is to move from output like this</p>
  
  <pre><code>FkId  Count
  1      42
  2      25
  </code></pre>
  
  <p>to output like this</p>
  
  <pre><code>FkId  Count  Name
  1      42     Ronald
  2      22     John
  </code></pre>
  
  <p>There are quite a few matches on SO for that error message, but some e.g. <a href="https://stackoverflow.com/a/6456944/141172">https://stackoverflow.com/a/6456944/141172</a> have comments like "will generate 3 scans on the table, rather than 1, so won't scale".</p>
  
  <p>How can I <em>efficiently</em> include a field from the joined <code>Table B</code> (which has a 1:1 relationship to <code>FkId</code>) in the query output?</p>