Aggregate Function Nesting Consideration in Oracle.

It’s been a while, I published blog on Oracle.
Today we will discuss on, nested aggregation functions and level it can be nested.

Lets first understand what is nesting functions.

Nested function is concept, when the output of one function is passed as input to other functions.

Function can be of two types
1. Scalar functions
2. Multi row functions / Aggregate function in oracle.

Lets take an example of scalar nested functions

select trunc(trim(to_char(sal,'9999.99'))) from emp;
 -- We nested up to three levels

In the above example, we are nesting scalar functions (act on 1 input and produce only 1 output).
Scalar functions can be nested multiple times, as long as no exceptions occurred. (Data type mismatch)

Now lets check same behaviour for aggregate functions and maximum nested level allowed.

To understand it better, let start with field work.
For the demo, we will consider employee table.

Suppose we are querying below SQL, using single aggregation functions.
Fetching Maximum salary earns in the department.

Select Deptno, Max(Sal) From Emp
 Group By Deptno;

DEPTNO MAX(SAL)
 ---------- ----------
 30 2850
 20 3000
 10 5000

Now let’s add one more aggregation function i.e. nesting aggregation functions.

Select Deptno,SUM(Max(Sal)) From Emp
 Group By Deptno;

Output:
 ORA-00937: not a single-group group function
 00937. 00000 - "not a single-group group function"

Reason for error:
By adding aggregate function “SUM”, we are moving aggregation to a higher level. An aggregation function SUM needs to act on the Set of rows return.
If you check above SQL output, we can conclude that SUM needs to act on below SET.

 DEPTNO MAX(SAL)
---------- ---------
30 2850
20 3000
10 500

But as we are displaying three different “DEPTNO” Column oracle can’t act on same to produce a single output for a set of inputs. Hence error!

Lets remove “DEPTNO” from projections.

Select SUM(Max(Sal)) From Emp
 Group By Deptno;

SUM(MAX(SAL))
 -------------
 10850

Now SQL works fine, as “SUM” Aggregation function acted on rows and produce a single output.
Hence aggregation on aggregation work fine i.e. Two level deep is possible provided we correct projected columns.

Now let try to increase aggregation levels.

Select count(SUM(Max(Sal))) From Emp
 Group By Deptno;

Error:
 ORA-00935: group function is nested too deeply
 00935. 00000 - "group function is nested too deeply"

Hence Two level Deep is the max you can go with nested aggregate functions in Oracle.
Logically that the way it should be, Third aggregation functions wont have any group to act on as previous aggregations functions “SUM” had already produce singular output.

Conclusion :
1. Two level deep is max nesting possible for aggregation function.
2. Modify Select as per aggregation levels to adhere proper grouping for final level.