Q:

Group by with a select column I cannot enter in the group by clause

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: 

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.

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.
My sql query
SELECT 
    first_name, 
    AVG(ISNULL(grade, 0)) AS average, 
    CASE    
        WHEN grade < 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

A:

Your query fails because you have an unaggregated column in the case expression (grade) that does not belong to the group by clause. In most databases, this is a fatal error - although MySQL is lax about that, which can lead to suprising behaviors.
Most likely, you want:
SELECT 
    s.first_name, 
    AVG(ISNULL(p.grade, 0)) AS average, 
    CASE WHEN AVG(ISNULL(p.grade, 0)) < 75 THEN 'failing' ELSE 'passing' END result 
FROM students s
LEFT JOIN papers p ON p.student_Id = s.id
GROUP BY s.id, s.first_name
ORDER BY average DESC

This sets the result according to the average grade of each student.
Note that I added table aliases to your query, and prefixed each column with the table it belongs to; this makes the query easier to read and write, and unambiguous about the underlying tables structure.
Side note: I am quite suspicious about the ISNULL() construct in aggregate function AVG(); AVG() ignore NULLs values, so your expression causes students without grades to get the 0 average instead of NULL- which may not be what you actually want.

