Nested group functions is quite a unique feature of Oracle SQL since (at least) version 7. However, it is not quite obvious where developers can benefit from using it. Below is one of the most frequent scenarios where the feature comes handy.
Suppose, you need to find a number of unique combinations of two or more columns in a table. This is a very frequent situation when you plan to create a new index on the selected columns.
Let say, we want to know how many different pairs of (deptno, job) fields we have in the emp table (scott schema). The most straight forward solution is:
SELECT COUNT(*) FROM (SELECT DISTINCT deptno, job FROM emp)
COUNT(*) -------- 9
The challenge is to do the same without the use of a sub-query (or in-line view/WITH clause):
SELECT COUNT(COUNT(*)) FROM emp GROUP BY deptno, job
COUNT(COUNT(*)) --------------- 9
You can use any “inner” group function with the same result:
SELECT COUNT(SUM(sal)) cnt1, COUNT(AVG(sal)) cnt2, COUNT(STDDEV(sal)) cnt3 FROM emp GROUP BY deptno, job
CNT1 CNT2 CNT3 ----- ---------- ---------- 9 9 9
P.S. If you like this trick, you can find many more in my book “Oracle SQL Tricks and Workarounds”