A Practical Use of Nested Group Functions in Oracle SQL, by Zahar Hilkevich

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)

Result:

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

Result:

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

Result:

 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”

Advertisements

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