Oracle:不是按表达错误分组 [英] Oracle: NOT A GROUP BY EXPRESSION ERROR

问题描述

以下是我的查询.我不确定是什么原因导致Not A group by expression错误.任何帮助深表感谢. =)

Below is my query. I am not sure what cause the Not A group by expression error. Any help is much appreciated. =)

SELECT c.courseID, c.courseName, AVG(a.Mark) as Average_Mark
FROM course c, assessment a
WHERE c.courseID = a.courseID
Group by c.courseID, c.courseName
ORDER BY Mark DESC;

推荐答案

尝试:

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

由于要汇总Mark的多个值以计算平均值，因此无法对Mark的每个值进行排序.您必须对计算结果进行排序，即Average_Mark.

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.

从更一般的角度来看，仅当此列是查询表的一部分并且不使用任何GROUP BY或DISTINCT时，才允许将ORDER BY用作非SELECT列(除非您GROUP BY此未显示的列，否则您可以ORDER BY对其进行修改.)

From a more general point of view, you are allowed to ORDER BY a non SELECTed 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).

原因很简单:如果使用GROUP BY或DISTINCT，则几行可能会显示为一行.这些合并"行中未显示的值可能彼此不同，从而使这些值上的任何ORDER BY都不可能.

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.

某些DBMS(至少是MySQL)的行为不同，即使使用GROUP BY，也允许ORDER设置BY未显示的值.但是MySQL似乎然后按照未显示值的第一个遇到的值进行排序(请参见 fiddle ) .因此，最好记住，应避免这种情况，以防止出现不可预测的结果.

Some DBMS (MySQL at least) behave differently, allowing ORDERing 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.

编辑:请参见有关MySQL GROUP BY处理的文档.

See the documentation about MySQL GROUP BY handling.

这篇关于Oracle:不是按表达错误分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！