title: Group by with a select column I cannot enter in the group by clause
body: <p>I wrote a query is SQL Server which selects the first_name from the customers table and then grade from the students table. It works fine without the CASE expression also works fine withing MySQL, however in SQL Server I seem to keep running into the following problem: </p>
  
  <blockquote>
    <p>Msg 8120, Level 16, State 1, Line 81
    Column 'papers.grade' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  </blockquote>
  
  <p>I do not really know why this is a problem and the only solution I found is including it in the group by as "GROUP BY first_name, grade". this however modifies my output and therefore is not usable. Could anyone please explain me why this error keeps happening? It'll be much appreciated.</p>
  
  <p>My sql query</p>
  
  <pre><code>SELECT 
      first_name, 
      AVG(ISNULL(grade, 0)) AS average, 
      CASE    
          WHEN grade &lt; 75 THEN'failing' 
          ELSE'passing' ENDAS result 
      FROM students 
      LEFT JOIN papers 
          ON papers.student_Id = students.id
          GROUP BY first_name
          ORDER BY average DESC
  </code></pre>