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.
One thought on “Nested Group Functions”
Certifications are so in lately, you have a certification on your resume, you have a substantial job. So, there is no wonder why you would be considering getting the Oracle Global Human Resources Cloud, after all it is one of the best.
But are you sure you are fully ready? 1z0-1049 Oracle Compensation Cloud 2019 Implementation Essentials demands considerable amount of your time. The thing that can lessen some of your strain is Latest 1z0-1049 Braindumps.
Many websites offer these 1z0-1049 Question Answers to make candidates understand the exam criteria beforehand. They are extremely helpful if this is your first certification exam. Through ORACLE 1z0-1049 Sample Questions you will get familiar with latest technology trends associated with your exam as well lots of other useful things. Including boosting up your confidence that you will give out a fantastic performance.