Advanced Oracle SQL: Counting, Nested Aggregates and PIVOT Exercises Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Exercises

1.     Counting

What is the difference between the following queries?

SELECT COUNT(*) FROM T;

And:

SELECT COUNT(*) FROM T GROUP BY 1;

And:

SELECT SUM(1) FROM T;

2.     Group

Which group by clause is expected in the following query?

SELECT EXTRACT(YEAR FROM HIREDATE), COUNT(*) FROM EMP GROUP BY

3.     Keep

Rewrite the following query without using a subquery (consider HIREDATE to be unique).

SELECT DEPTNO, ENAME FROM EMP
WHERE
  (DEPTNO, HIREDATE) IN (SELECT DEPTNO,MIN(HIREDATE) FROM EMP GROUP BY DEPTNO)

4.     Nested aggregates

What is missing in the following query?

SELECT MAX(AVG(SAL)) FROM EMP

5.     Subtotals

How many rows will be returned by the following query?

WITH T AS (SELECT 1 NUM, 'RED' COL, 'EMPTY' SHADE, 'CIRCLE' SYMBOL FROM DUAL
UNION ALL SELECT 3, 'BLUE', 'FULL', 'SQUARE' FROM DUAL)
SELECT T.*, COUNT(*) FROM T GROUP BY CUBE(NUM, COL, SHADE, SYMBOL)

6.     Pivot

Rewrite the following query using the pivot operator.

SELECT
   DEPTNO,
   COUNT(CASE WHEN SAL BETWEEN 0 AND 999 THEN 1 END) "0-999",
   COUNT(CASE WHEN SAL BETWEEN 1000 AND 1999 THEN 1 END) "1000-1999",
   COUNT(CASE WHEN SAL BETWEEN 2000 AND 2999 THEN 1 END) "2000-2999",
   COUNT(CASE WHEN SAL BETWEEN 3000 AND 3999 THEN 1 END) "3000-3999"
FROM EMP
GROUP BY DEPTNO

7.     Unpivot

What is the result of the following query?

SELECT MAX(ROWNUM) FROM DUAL UNPIVOT(X FOR Y IN (DUMMY,DUMMY,DUMMY))

Solutions

1.     Counting

SELECT COUNT(*) FROM T;

SELECT COUNT(*) FROM T GROUP BY 1;

SELECT SUM(1) FROM T;

The three queries above are very similar and will count the rows in the tables.  The first query returns the number of rows in the table T. the expected result may be 0, 1, 14 or any positive number.

The second query groups the rows in a group and for this group, returns the number of rows. If there is no row, there will be no group and therefore, no rows will be returned.

In the third query, the value 1 is attributed to each row and the sum is the number of the rows. When no row exists, no value will be attributed and therefore, SUM will return NULL.

CREATE TABLE

   T
(
   X NUMBER
);

Table created.

SELECT COUNT(*) FROM T;
  COUNT(*)
----------
         0

SELECT COUNT(*) FROM T GROUP BY 1;

no rows selected

SELECT SUM(1) FROM T;

    SUM(1)
----------
<NULL>

2.  Group

SELECT EXTRACT(YEAR FROM HIREDATE), COUNT(*) FROM EMP GROUP BY

It is syntaxically correct to group by the hire date. To group by the year, the extract function is used as a group:

SELECT
   EXTRACT(YEAR FROM HIREDATE),
   COUNT(*)
FROM
   EMP
GROUP BY
   HIREDATE;

EXTRACT(YEARFROMHIREDATE)   COUNT(*)
------------------------- ----------
                     1980          1
                     1981          2
                     1981          1
                     1981          1
                     1981          1
                     1981          1
                     1981          1
                     1981          1
                     1981          1
                     1981          1
                     1982          1
                     1987          1
                     1987          1

SELECT
   EXTRACT(YEAR FROM HIREDATE),
   COUNT(*)
FROM
   EMP
GROUP BY
   EXTRACT(YEAR FROM HIREDATE;

EXTRACT(YEARFROMHIREDATE)   COUNT(*)
------------------------- ----------
                     1980          1
                     1981         10
                     1982          1
                     1987          2

The second query returns the expected results.

3.     Keep

SELECT DEPTNO, ENAME FROM EMP
WHERE
  (DEPTNO, HIREDATE) IN (SELECT DEPTNO,MIN(HIREDATE) FROM EMP GROUP BY DEPTNO)

The first employee of each department of EMP can be retrieved by using the KEEP clause.

SELECT
   DEPTNO,
   MAX(ENAME) KEEP (DENSE_RANK FIRST ORDER BY HIREDATE)
FROM
   EMP
GROUP BY
   DEPTNO;

DEPTNO MAX(ENAME)
------ ----------
    10 CLARK
    20 SMITH
    30 ALLEN

4.     Nested aggregates

SELECT MAX(AVG(SAL)) FROM EMP
SELECT MAX(AVG(SAL)) FROM EMP
           *
ERROR at line 1:
ORA-00978: nested group function without GROUP BY

Nested aggregate requires a GROUP BY clause.

5.     Subtotals

WITH T AS (SELECT 1 NUM, 'RED' COL, 'EMPTY' SHADE, 'CIRCLE' SYMBOL FROM DUAL
UNION ALL SELECT 3, 'BLUE', 'FULL', 'SQUARE' FROM DUAL)
SELECT T.*, COUNT(*) FROM T GROUP BY CUBE(NUM, COL, SHADE, SYMBOL)

With four columns and one row, there are 16 rows (2⁴): the group itself, 14 subtotals and the grand total. With two distinct rows with all values that are distinct, there are 16*2 rows, minus one, because the grand total is aggregating the two distinct rows in one. Therefore, 31 rows are selected.

       NUM COL  SHADE SYMBOL   COUNT(*)
---------- ---- ----- ------ ----------
                                      2
                      CIRCLE          1
                      SQUARE          1
                FULL                  1
                FULL  SQUARE          1
                EMPTY                 1
                EMPTY CIRCLE          1
           RED                        1
           RED        CIRCLE          1
           RED  EMPTY                 1
           RED  EMPTY CIRCLE          1
           BLUE                       1
           BLUE       SQUARE          1
           BLUE FULL                  1
           BLUE FULL  SQUARE          1
         1                            1
         1            CIRCLE          1
         1      EMPTY                 1
         1      EMPTY CIRCLE          1
         1 RED                        1
         1 RED        CIRCLE          1
         1 RED  EMPTY                 1
         1 RED  EMPTY CIRCLE          1
         3                            1
         3            SQUARE          1
         3      FULL                  1
         3      FULL  SQUARE          1
         3 BLUE                       1
         3 BLUE       SQUARE          1
         3 BLUE FULL                  1
         3 BLUE FULL  SQUARE          1 

31 rows selected.

6.     Pivot

SELECT
   DEPTNO,
   COUNT(CASE WHEN SAL BETWEEN 0 AND 999 THEN 1 END) "0-999",
   COUNT(CASE WHEN SAL BETWEEN 1000 AND 1999 THEN 1 END) "1000-1999",
   COUNT(CASE WHEN SAL BETWEEN 2000 AND 2999 THEN 1 END) "2000-2999",
   COUNT(CASE WHEN SAL BETWEEN 3000 AND 3999 THEN 1 END) "3000-3999"
FROM EMP
GROUP BY DEPTNO

The pivot aggregate function is COUNT and the transposed column is the salary:

SELECT
   *
FROM
(
   SELECT
      DEPTNO,
      TRUNC(SAL,-3) SAL
   FROM
      EMP
)
PIVOT
(
   COUNT(*)
   FOR
      (SAL)
   IN
      (0,1000,2000,3000)
);

    DEPTNO          0       1000       2000       3000
---------- ---------- ---------- ---------- ----------
        30          1          4          1          0
        20          1          1          1          2
        10          0          1          1          0

7.     Unpivot

SELECT MAX(ROWNUM) FROM DUAL UNPIVOT(X FOR Y IN (DUMMY,DUMMY,DUMMY))

For each column DUMMY, DUMMY and DUMMY, a row is generated and the highest ROWNUM is 3.