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?

CodePudding user response：

Oracle doesn't allow for nesting two aggregate functions in this way. 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.

CodePudding user response：

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;

Fiddle