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

Question

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?

Answer 1

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.