Advanced Oracle SQL: Standard Aggregate Functions Oracle Tips by Laurent Schneider

 

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

An aggregate function in SQL is a function that returns a single value from multiple rows.

Standard Aggregate Functions

An aggregate function can be used over the whole table to return a single value:

SELECT
   COUNT(ENAME)
FROM
   EMP;
COUNT(ENAME)
------------
          14

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    84 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------- 

The number of employees in the EMP table is returned.  

SELECT
   DEPTNO,
   SUM(SAL)
FROM
   EMP
GROUP BY
   DEPTNO;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

The group is the department. Note the operation HASH GROUP BY in the execution plan. This operation appeared in 10gR2 and requires no sorting.

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

In earlier releases, the operation to group the rows was a SORT GROUP BY operation and it implied sorting. This optimization affected applications that rely on implicit sorting. In 10gR2 and later, sorting is achieved by using the ORDER BY clause.

Multiple aggregate functions can be used in the same query. For example, the maximum and the minimum salary can be returned as a single row:

SELECT
   MIN(SAL),
   MAX(SAL)
FROM
   EMP;
  MIN(SAL)   MAX(SAL)
---------- ----------
       800       5000

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Both values are returned. The function AVG computes the average:

SELECT
   COUNT(*),
   COUNT(COMM),
   SUM(COMM),
   AVG(COMM)
FROM
   EMP;

  COUNT(*) COUNT(COMM)  SUM(COMM)  AVG(COMM)
---------- ----------- ---------- ----------
        14           4       2200        550

Notice the null values are ignored; only the not null values are taken to evaluate the average. Null values are never aggregated. 

COUNT(*) is a special syntax that instructs Oracle to count all rows including nulls.

STATS_MODE is a very efficient function which returns the mode value for a column, i.e. the value that appears most frequently.

SELECT
   STATS_MODE(DEPTNO)
FROM
   EMP;
STATS_MODE(DEPTNO)
------------------
                30

The department with the most employees is returned. The function is not deterministic. In case of a tie, the chosen modal value may differ from one execution to another.