获得“不是单组的组函数”错误。所有非聚合列均已在分组依据中列出

Question

SELECT    STATUS, 
          AVG(COUNT(CRIME_ID)) "Avg # of Crimes per Status"
FROM      CRIMES
GROUP BY  STATUS;

当我尝试运行它时，我得到“不是按功能进行的单组分组”。正如您所看到的，我已将非聚合列包含在分组依据列表中。当它从 SELECT 列表中删除时，它会运行，但我需要用它来运行它。问题是什么？

Answer 1

Oracle 实际上确实允许在其早期版本中嵌套两个聚合函数，但这样做意味着存在两个聚合，第一个聚合带有 GROUP BY 子句，而后面的一个则没有。
这意味着我们无法选择 GROUP BY 中的列 -
这就是错误的根源。

通过从 SELECT 子句中删除 STATUS 列，我们得到一个有效的查询：

SELECT    AVG(COUNT(CRIME_ID)) "Avg # of Crimes per Status"
FROM      CRIMES
GROUP BY  STATUS;

Avg # of Crimes per Status
3.3333333333333333333333333333333333333

小提琴

“可以嵌套聚合函数。例如以下示例
计算所有部门最高工资的平均值
在斯科特模式中：

从员工组中按部门 ID 选择 AVG(MAX(薪水))；

平均（最高（工资））
`----------------
10925

此计算评估每个的内部聚合 (MAX(sal))
由 GROUP BY 子句 (deptno) 定义的组，并聚合
再次结果。”

Oracle9i SQL 参考版本 1 ( 9.0.1)

Answer 2

在选择组时，Oracle 不允许以这种方式嵌套两个聚合函数。对计数查询进行子查询，然后取平均值：

SELECT AVG(cnt)
FROM
(
    SELECT STATUS, COUNT(CRIME_ID) AS cnt
    FROM CRIMES
    GROUP BY STATUS
) t;

请注意，您可以尝试：

SELECT AVG(COUNT(CRIME_ID)) OVER () AS avg
FROM CRIMES
GROUP BY STATUS
ORDER BY STATUS
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

这里我们使用 AVG() 作为结果集中所有组的分析函数。然后，我们将输出限制为一行。