6.1 Aggregate Functions & NULL Mechanics

6.1 Aggregate Functions & NULL Mechanics

In relational database management, data analysis frequently requires calculating summary metrics across sets of records rather than transforming individual row values. Oracle SQL distinguishes sharply between single-row functions (which return one result for every individual row processed) and group functions (also known as aggregate functions or multi-row functions), which operate on collections of rows to yield a single summarized result per group or entire table.

Mastering group functions for the Oracle Database SQL Certified Associate (1Z0-071) exam requires understanding their mathematical mechanics, valid input datatypes, distinct modifiers, and—most importantly—their specialized behavior when encountering NULL values.

---

Overview of Oracle Group Functions

Oracle SQL provides a comprehensive suite of built-in aggregate functions for mathematical, statistical, and data-gathering operations:

+-----------------------------------------------------------------------------------+
|                             ORACLE GROUP FUNCTIONS                                |
+---------------------+-------------------------------------------------------------+
| Function            | Description & Primary Purpose                               |
+---------------------+-------------------------------------------------------------+
| AVG(expr)           | Calculates the mathematical mean of expr                    |
| SUM(expr)           | Computes the arithmetic sum of expr                         |
| COUNT(*)            | Returns the total number of rows retrieved                  |
| COUNT(expr)         | Returns the number of non-NULL occurrences of expr          |
| MIN(expr)           | Identifies the lowest value of expr                         |
| MAX(expr)           | Identifies the highest value of expr                        |
| MEDIAN(expr)        | Calculates the middle value or 50th percentile of expr      |
| STDDEV(expr)        | Computes the sample standard deviation of expr              |
| VARIANCE(expr)      | Computes the sample statistical variance of expr            |
+---------------------+-------------------------------------------------------------+

Syntax and Options

The standard syntax for group functions in Oracle SQL is:

aggregate_function([DISTINCT | ALL] expression)

• ALL (Default): Directs the aggregate function to include all non-null values, including duplicate values. If omitted, ALL is assumed automatically.
• DISTINCT: Forces the function to consider only unique, non-null values, discarding duplicates before performing the computation.
• expression: Represents a column name, constant, or arithmetic/character expression. Aggregate functions cannot operate directly on nested aggregate functions unless accompanied by a GROUP BY clause.

---

Datatype Compatibility and Return Types

Not all aggregate functions accept all datatypes. The 1Z0-071 exam regularly tests whether a given group function can be applied to character strings, dates, or numeric values.

Character and Date Collation in MIN / MAX

When MIN and MAX are applied to non-numeric columns, Oracle determines ordering based on the binary ASCII/Unicode encoding or session linguistic sort:

-- Evaluating MIN/MAX across character and date columns
SELECT 
    MIN(last_name)  AS first_alphabetical_name,
    MAX(last_name)  AS last_alphabetical_name,
    MIN(hire_date)  AS earliest_hired_employee,
    MAX(hire_date)  AS most_recent_hire
FROM employees;

• MIN(last_name) returns 'Abel' (lowest alphabetical sort value).
• MAX(last_name) returns 'Zlotkey' (highest alphabetical sort value).
• MIN(hire_date) returns '13-JAN-2001' (the earliest chronological date).
• MAX(hire_date) returns '21-APR-2008' (the latest chronological date).

---

The Three Variants of COUNT

The COUNT function behaves differently depending on whether an asterisk (*), an expression (expr), or the DISTINCT keyword is passed as an argument. Understanding these differences is critical for exam success.

+-----------------------------------------------------------------------------------+
|                                COUNT VARIATIONS                                   |
+---------------------+-------------------------------------------------------------+
| COUNT(*)            | Counts EVERY row in the result set, including rows where    |
|                     | individual or all column values are NULL.                   |
+---------------------+-------------------------------------------------------------+
| COUNT(expr)         | Evaluates expr for each row and counts ONLY rows where expr |
|                     | is NOT NULL. Duplicate non-null values are counted.         |
+---------------------+-------------------------------------------------------------+
| COUNT(DISTINCT expr)| Evaluates expr, filters out duplicate values, and counts    |
|                     | ONLY unique non-NULL occurrences.                           |
+---------------------+-------------------------------------------------------------+

Practical Demonstration: Sample Dataset Walkthrough

Consider the following sample table SAMPLE_STAFF containing 6 rows:

Let us evaluate the results of different COUNT expressions on SAMPLE_STAFF:

SELECT 
    COUNT(*)                          AS total_rows,
    COUNT(department_id)              AS count_dept,
    COUNT(DISTINCT department_id)     AS count_dist_dept,
    COUNT(commission_pct)             AS count_comm,
    COUNT(DISTINCT commission_pct)    AS count_dist_comm
FROM sample_staff;

Evaluation Walkthrough:

1. COUNT(*): Evaluates all rows in the table. Result = 6.
2. COUNT(department_id): Rows 101, 102, 103, 104, and 106 have non-null department IDs (10, 10, 20, 20, 20). Row 105 is NULL. Result = 5.
3. COUNT(DISTINCT department_id): Non-null values are 10 and 20. Result = 2.
4. COUNT(commission_pct): Rows 101 (0.20), 102 (0.10), and 103 (0.20) are non-null. Rows 104, 105, and 106 are NULL. Result = 3.
5. COUNT(DISTINCT commission_pct): Unique non-null values are 0.20 and 0.10. Result = 2.

---

Automatic NULL Exclusion & The NVL Solution

A fundamental axiom of Oracle SQL group functions is:

The NULL Handling Rule: All aggregate functions (AVG, SUM, COUNT(expr), MIN, MAX, MEDIAN, STDDEV, VARIANCE) automatically ignore NULL values when computing results. The only exception is COUNT(*), which counts rows regardless of column contents.

Impact on Averages: AVG(col) vs. AVG(NVL(col, 0))

Because AVG calculates the arithmetic mean by dividing the sum of values by the number of non-null values, omitting NULL values can significantly alter business calculations:

$\text{AVG}(\text{expr}) = \frac{\sum \text{Non-NULL Values}}{\text{COUNT}(\text{expr})}$

$\text{AVG}(\text{NVL}(\text{expr}, 0)) = \frac{\sum \text{Non-NULL Values} + 0}{\text{COUNT}(*)}$

Using our SAMPLE_STAFF table where COMMISSION_PCT values are [0.20, 0.10, 0.20, NULL, NULL, NULL] across 6 employees:

SELECT 
    AVG(commission_pct)              AS avg_comm_active,
    AVG(NVL(commission_pct, 0))       AS avg_comm_all_staff
FROM sample_staff;

1. Calculation for AVG(commission_pct):

   • Sum of non-null values: $0.20 + 0.10 + 0.20 = 0.50$
   • Count of non-null values: $3$
   • Result: $\frac{0.50}{3} \approx \mathbf{0.1667}$
   • Business Meaning: The average commission earned among employees eligible for commission.

2. Calculation for AVG(NVL(commission_pct, 0)):

   • NVL substitutes 0 for the 3 NULL rows: [0.20, 0.10, 0.20, 0, 0, 0]
   • Sum of values: $0.20 + 0.10 + 0.20 + 0 + 0 + 0 = 0.50$
   • Count of values: $6$
   • Result: $\frac{0.50}{6} \approx \mathbf{0.0833}$
   • Business Meaning: The average commission across the entire workforce, including non-commissioned staff.

Exam Trap: When a question asks for "the average bonus across all employees in the department," examine whether employees with NULL bonuses should be treated as having earned zero. If yes, the query must wrap the column in NVL(bonus, 0) or COALESCE(bonus, 0).

---

Aggregate Behavior on All-NULL Datasets or Empty Sets

What happens when a query aggregates a table that contains zero matching rows, or when every value in the selected column is NULL?

-- Querying against an empty subset
SELECT 
    COUNT(*)        AS cnt_all,
    COUNT(salary)   AS cnt_sal,
    SUM(salary)     AS sum_sal,
    AVG(salary)     AS avg_sal,
    MIN(salary)     AS min_sal,
    MAX(salary)     AS max_sal
FROM employees
WHERE department_id = 9999; -- No rows match this condition

SUMMARY OF EMPTY / ALL-NULL SET BEHAVIOR:
+---------------------------+----------------+
| Function                  | Result         |
+---------------------------+----------------+
| COUNT(*) / COUNT(expr)    | 0              |
| SUM, AVG, MIN, MAX        | NULL           |
| MEDIAN, STDDEV, VARIANCE  | NULL           |
+---------------------------+----------------+

If an application requires SUM or AVG to return 0 instead of NULL when no rows exist, the aggregate function must be wrapped in NVL: NVL(SUM(salary), 0).