Oracle actually does allow for nesting two aggregate functions from its earlier versions, but doing so means that there are two aggregations, the first one with a GROUP BY clause but the one following it - without.
This means we cannot select the columns in the GROUP BY -
and that is the source of the error.
By removing the STATUS
column from the SELECT
clause, we get a valid query:
SELECT AVG(COUNT(CRIME_ID)) "Avg # of Crimes per Status"
FROM CRIMES
GROUP BY STATUS;
Avg # of Crimes per Status |
3.33333333333333333333333333333333333333 |
Fiddle
"You can nest aggregate functions. For example, the following example
calculates the average of the maximum salaries of all the departments
in the scott schema:
SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;
AVG(MAX(SALARY))
`----------------
10925
This calculation evaluates the inner aggregate (MAX(sal)) for each
group defined by the GROUP BY clause (deptno), and aggregates the
results again."
Oracle9i SQL Reference Release 1 (9.0.1)