ORA-00937: not a single-group group function说明及解决方法(E文)

最新推荐文章于 2022-02-19 16:02:39 发布

转载最新推荐文章于 2022-02-19 16:02:39 发布 · 1.4w 阅读

Oracle 专栏收录该内容

224 篇文章

订阅专栏

本文详细解释了Oracle数据库中ORA-00937错误的原因及其解决方法，当SQL查询同时包含聚合函数和未在GROUP BY子句中指定的列时会触发此错误。文章通过具体示例展示了如何正确地使用GROUP BY子句来避免该错误。

ORA-00937: not a single-group group function说明及解决方法(E文)

转载 2009年03月15日 21:17:00

标签：
function /
include /
list /
user /
sql

A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.

Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed.

eg:

SQL> select owner, count(*)
2 from dba_segments;
select owner, count(*)
*
ERROR at line 1:
ORA-00937: not a single-group group function

Because we have a group function (COUNT) in our column list, and an additional column (OWNER), we have to include the GROUP BY clause in our select statement.

SQL> select owner, count(*)
  2 from dba_segments
3 group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
USER1                              166
USER2                              166