Why GROUP BY is needed for a nested aggregate function in Oracle

user_8473984 0
Viewed 1058

I have a table named SHIP_CABINS and its structure is like this:

Name          Null?    Type         
------------- -------- ------------ 
SHIP_CABIN_ID NOT NULL NUMBER       
ROOM_NUMBER            NUMBER       
TYPE                   VARCHAR2(10) 
SQ_FT                  NUMBER

I can issue the following queries without using a GROUP BY clause:

SELECT MAX(SQ_FT)
FROM SHIP_CABINS;

SELECT COUNT(*)
FROM SHIP_CABINS;

However, if I nest MAX and COUNT function, I have to use a GROUP BY clause. The following query will fail:

SELECT MAX(COUNT(*))
FROM SHIP_CABINS;

If my table contains 10 rows, why does the above query run without a GROUP BY clause and simple returns 10?

oracle sql
2 Answers

Well the error message you get says it ORA-00978: nested group function without GROUP BY

If you check the documentation there is not an explicit notion of this limitation, but carefully reading the description of the functionality you should realize, that the Group byclause is required for the usage of the nested aggregate functions.

You can nest aggregate functions ... This calculation evaluates the inner aggregate (MAX(salary)) for each group defined by the GROUP BY clause ..., and aggregates the results again.

So you have two workarounds to simulate the nested aggregation without GROUP BY

A) Add constant GROUP BY

select max(count(*)) from tab group by 42;

Note that you must use NLV if you require a zero result on the empty table (same as in case B)

select nvl(max(count(*)),0) max_cnt from tab group by 42

B) Split in two Subqueries

with tab2 as (
select count(*) cnt from tab)
select max(cnt) max_cnt
from tab2
user_48081220

First note that simply selecting an aggregate like count(*) with no group by clause is not always equivalent to aggregating over all the rows in the table. Namely, the only exception is when the table is empty (or the query includes a where clause that filters out all the rows). With group by 'x' (or some other constant that guarantees that all rows are in a single group - I personally like group by null) the query returns no rows, because there are no groups. Without a group by clause, however, the aggregate query does return a row - it will show count(*) of 0, for example. Try to remember this when you use aggregate functions without group by and you start seeing odd results.

Now back to your question. There is no logical reason Oracle (or the SQL standard) can't allow nested aggregate functions without group by. Let the inner function be calculated over the base table, and the outer aggregate be calculated over the result set of the first aggregation. Why Oracle chose not to allow it is a good question for Oracle.

However, if for some reason you must do what you showed, you can change the max() call from an aggregate function to an analytic function, as shown below. The inner aggregation always returns exactly one row (even when the base table is empty, or there is a where clause that filters out all the rows), so the "analytic function" is over a single row - there is no performance hit.

select max(count(*)) over ()       -- notice the (empty!) analytic clause
from   ship_cabins;
Admin1
Related