Aggregate Function Nesting 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 5000

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.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s