Getting a "not a single-group group function" error. All non-aggregate columns have been listed in the group by

user_12180132 0
Viewed 59

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

When I try to run this I get the "not a single-group group by function". As you can see I have included the non-aggregated column in the group by list. When it is removed from the SELECT list it runs, but I need to run it with it. What is the problem?

ora-00937 group-by oracle sql
2 Answers

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)

user_63364790

Oracle doesn't allow for nesting two aggregate functions in this way when also selecting the group. Subquery the count query and then take the average:

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

Note that you could try:

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

Here we are using AVG() as an analytic function over all groups in the result set. Then, we limit the output to just one row.

user_18632290
Related