Oracle: NOT A GROUP BY EXPRESSION ERROR

oracle, sql

Solution

Try:

SELECT c.courseid,
       c.coursename,
       AVG(a.mark) AS Average_Mark
FROM   COURSE c
       INNER JOIN ASSESSMENT a
               ON c.courseid = a.courseid
GROUP  BY c.courseid,
          c.coursename
ORDER  BY 3 DESC; -- or ORDER BY Average_Mark DESC

As you aggregate several values of `Mark` in order to compute the average, it becomes impossible to sort on each value of `Mark`. You have to sort on the result of the computation, i.e. `Average_Mark`.

From a more general point of view, you are allowed to `ORDER BY` a non `SELECT`ed column only if this column is part of the queried tables and if you don't use any `GROUP BY` or `DISTINCT` (unless you `GROUP BY` this non displayed column, then you can `ORDER BY` it).

The reason is simple: If you use `GROUP BY` or `DISTINCT`, several rows will be potentially displayed as one. Non displayed values in those "merged" rows can potentially be different from each other, making any `ORDER BY` impossible on those values.

Some DBMS (MySQL at least) behave differently, allowing `ORDER`ing `BY` non displayed values, even with `GROUP BY`. But MySQL seems then to order by the first encountered value of non displayed value (see fiddle). So, better keep in mind that this should be avoided, to prevent unpredictible results.

EDIT: See the documentation about MySQL `GROUP BY` handling.

Problem

Original source