NESTED GROUP FUNCTIONS (2 levels deep)
Group functions may only be nested two levels deep.
An example could be the following :
select SUM(AVG(salary)) from employees group by department_id;
So, first of all, let’s determine what this SQL statement with two nested group functions is supposed to do? Obviously, the average salary per department will be calculated first. At the second step, the summary of all average salaries will be produced. Very important notice here: GROUP BY clause is mandatory since the average value of the salary per department is calculated before being aggregated by the SUM function. If you omit the GROUP BY clause in SQL statements with nested group functions, it will give you an error ORA-00978 ( see below )
Let’s define what is going on, and how it works. First, let’s see how Oracle performs the first step in this SQL statement: calculating the average salary per department
The second step would be applying another group function to this set of data. The next group function is SUM. So the task of SUM function is to calculate the sum of these 12 average salaries and produce a single value as result.
The third form is disallowed by Oracle. Consider an expression that nests three
group functions. If the MAX function is applied to the previous example, the expression
MAX(SUM(AVG(salary))) is formed. The two inner group functions return a
single value representing the sum of a set of average values. This expression becomes
MAX(single value). A group function cannot be applied to a single value.
select max(sum(avg(salary))) from employees group by department_id;
SINGLE GROUP FUNCTIONS (NOT NESTED)
As we know, if SQL statement contains only one group function, then GROUP BY clause is not necessary at all; (see below)
Moreover, we certainly can have multiple group functions in one SQL statement, and all of them will work without applying a GROUP BY clause. (see below )
NESTED GROUP FUNCTIONS (3 levels deep)
This form is disallowed by Oracle. Consider an expression that nests three group functions. If the MAX function is applied to the previous example, the expression MAX(SUM(AVG(group_item))) is formed. The two inner group functions return a single value representing the sum of a set of average values. This expression becomes MAX(single value). A group function cannot be applied to a single value. This is what the Roopesh Ramklass book says about it. That was the reason why Oracle complained with “group function is nested too deeply”
The outermost group function which is MAX gets only one argument to manage while expecting at least two. But, in fact, if you check it, you will quickly realize that Roopesh Ramklass statement is not true. Group function can be applied to a single value. (see below)
So, Oracle does not allow group functions more than two levels deep because it does not make any sense to have more levels of aggregation. After getting a single value from two nested group functions, all subsequent group functions won’t change this value. In the example above with select SUM(AVG(salary) from employees GROUP BY department_id; we are getting 97830 as a result. So, it does not matter what next group function would be applied to this value SUM, MAX or MIN – it will produce the same value anyway.