title: How do I select columns together with aggregate functions?
body: <p>Let's say I have a table of companies:</p>
  
  <pre><code>Company
  coID | coName | coCSR
  </code></pre>
  
  <p>The <code>coCSR</code> field is a numeric ID which relates to the account handler table:</p>
  
  <pre><code>AccountHandler
  ahID | ahFirstName | ahLastName
  </code></pre>
  
  <p>I also have a table of orders:</p>
  
  <pre><code>Order
  orID | orCompanyID | orDate | orValue
  </code></pre>
  
  <p>Now what I need to produce is output structured as follows:</p>
  
  <pre><code>Company | Account handler | No. of orders | Total of orders
  </code></pre>
  
  <p>Here is the query I have tried, which produces an error:</p>
  
  <pre><code>SELECT coID, coName, ahFirstName+' '+ahLastName AS CSRName, COUNT(orID) AS numOrders, SUM(orValue) AS totalRevenue
  FROM Company
  LEFT JOIN AccountHandler ON coCSR = ahID
  LEFT JOIN Order ON coID = orCompanyID
  WHERE coCSR = 8
  AND orDate &gt; getdate() - 365
  ORDER BY coName ASC
  </code></pre>
  
  <p>The error is: <strong>Column name 'AccountHandler.ahLastName' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.</strong></p>
  
  <p>If I use <code>GROUP BY coID</code>, I get <strong>Incorrect syntax near the keyword 'WHERE'.</strong> If I change the <code>WHERE</code> to <code>HAVING</code> because of the aggregate functions, I get errors telling me to remove each of the other column names that aren't contained in either an aggregate function or the GROUP BY clause.</p>
  
  <p>I have to admit, I don't yet understand the logic and syntax of anything but the most basic SQL commands, I'm just trying to apply what I've seen used before, and it's not working. Please help me to get this working. Better still, can you help me understand why it doesn't work at the moment? :)</p>