You issue the following query:
SQL> SELECT AVG(MAX(qty))
FROM ord_items
GROUP BY item_no
HAVING AVG(MAX(qty))>50;
Which statement is true regarding the outcome of this query?
A.
It executes successfully and gives the correct output.
B.
It gives an error because the HAVING clause is not valid.
C.
It executes successfully but does not give the correct output.
D.
It gives an error because the GROUP BY expression is not valid.
We cannot give nested function in HAVING clause?
in this case you cannot use nested functions. When you have nested function ‘group by’ clause is needed. For example:
1. Main select statement without ‘group by’
select avg(max(sal))
from emp;
ORA-00978: nested group function without GROUP BY
00978. 00000 – “nested group function without GROUP BY”
select avg(max(sal))
from emp
group by empno;
AVG(MAX(SAL))
————-
2073.214286
2. Select with ‘having’ clause
select avg(max(sal))
from emp
group by empno
having avg(max(sal))>1000
ORA-00935: group function is nested too deeply
00935. 00000 – “group function is nested too deeply”
*Cause:
*Action:
Error at Line: 4 Column: 12
Group functions may only be nested two levels deep. Three formats using group
functions are shown here:
G1(group_item) = result
G1(G2(group_item ) = result
G1(G2(G3(group_item))) is NOT allowed.
Group functions are represented by the letter G followed by a number. The first
simple form contains no nested functions. Examples include the SUM(group_item)
or AVG(group_item) functions that return a single result per group. The second form
supports two nested group functions, like SUM(AVG(group_item)). In this case, a
GROUP BY clause is mandatory since the average value of the group_item per group
is calculated before being aggregated by the SUM function.