ExamFinch.com My Study

Study guide

6.1 Aggregate Functions & NULL Mechanics

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

6.1 Aggregate Functions & NULL Mechanics

Key Takeaways Explain This Topic AI

Oracle group (aggregate) functions operate across sets of rows to return a single summarized result per group or table.

COUNT(*) counts all rows including duplicates and NULLs, COUNT(expr) counts non-NULL occurrences of expr, and COUNT(DISTINCT expr) counts unique non-NULL values.

All aggregate functions except COUNT(*) automatically ignore NULL values during calculation; use NVL or COALESCE to force NULLs into calculations (e.g., AVG(NVL(comm, 0))).

If an aggregate function processes a dataset where all input values are NULL (or zero rows match), COUNT returns 0, while all other group functions (SUM, AVG, MIN, MAX, MEDIAN, STDDEV, VARIANCE) return NULL.

SUM, AVG, STDDEV, and VARIANCE require numeric datatypes, whereas MIN and MAX operate on numeric, character, and datetime datatypes.

Last updated: August 2026

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.

Function Allowed Input Datatypes Return Datatype Behavior / Collation Rules

AVG NUMBER , FLOAT , BINARY_FLOAT , BINARY_DOUBLE Numeric ( NUMBER or binary float) Computes mean; non-numeric types raise ORA-00932: inconsistent datatypes .

SUM NUMBER , FLOAT , BINARY_FLOAT , BINARY_DOUBLE Numeric ( NUMBER or binary float) Computes total; non-numeric types raise ORA-00932 .

COUNT Any datatype ( CHAR , VARCHAR2 , NUMBER , DATE , TIMESTAMP , CLOB , BLOB ) NUMBER Returns integer count of rows or non-null values.

MIN NUMBER , CHAR , VARCHAR2 , DATE , TIMESTAMP , INTERVAL Same as input datatype Lowest number, earliest date/time, or lowest character string in collation order.

MAX NUMBER , CHAR , VARCHAR2 , DATE , TIMESTAMP , INTERVAL Same as input datatype Highest number, latest date/time, or highest character string in collation order.

MEDIAN NUMBER , DATE , TIMESTAMP , INTERVAL NUMBER or Datetime Continuous percentile calculation; character datatypes raise ORA-00932 .

STDDEV NUMBER , FLOAT , BINARY_FLOAT , BINARY_DOUBLE NUMBER or binary float Square root of variance; numeric only.

VARIANCE NUMBER , FLOAT , BINARY_FLOAT , BINARY_DOUBLE NUMBER or binary float Sample variance; numeric only.

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:

  • EMP_ID FIRST_NAME DEPARTMENT_ID COMMISSION_PCT SALARY
  • 101 Neena 10 0.20 10000
  • 102 Lex 10 0.10 12000
  • 103 John 20 0.20 8000
  • 104 Karen 20 NULL 9000
  • 105 Jon NULL NULL 7500
  • 106 David 20 NULL 6500
  • 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:

COUNT(*) : Evaluates all rows in the table. Result = 6 .

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 .

COUNT(DISTINCT department_id) : Non-null values are 10 and 20 . Result = 2 .

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 .

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: AVG ( expr ) = ∑ Non-NULL Values COUNT ( expr ) \text{AVG}(\text{expr}) = \frac{\sum \text{Non-NULL Values}}{\text{COUNT}(\text{expr})} AVG ( expr ) = COUNT ( expr ) ∑ Non-NULL Values ​ AVG ( NVL ( expr , 0 ) ) = ∑ Non-NULL Values + 0 COUNT ( ∗ ) \text{AVG}(\text{NVL}(\text{expr}, 0)) = \frac{\sum \text{Non-NULL Values} + 0}{\text{COUNT}(*)} AVG ( NVL ( expr , 0 )) = COUNT ( ∗ ) ∑ Non-NULL Values + 0 ​

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;

Calculation for AVG(commission_pct) :

  • Sum of non-null values: 0.20 + 0.10 + 0.20 = 0.50 0.20 + 0.10 + 0.20 = 0.50 0.20 + 0.10 + 0.20 = 0.50
  • Count of non-null values: 3 3 3
  • Result: 0.50 3 ≈ 0.1667 \frac{0.50}{3} \approx \mathbf{0.1667} 3 0.50 ​ ≈ 0.1667

Business Meaning: The average commission earned among employees eligible for commission.

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 0.20 + 0.10 + 0.20 + 0 + 0 + 0 = 0.50 0.20 + 0.10 + 0.20 + 0 + 0 + 0 = 0.50

  • Count of values: 6 6 6
  • Result: 0.50 6 ≈ 0.0833 \frac{0.50}{6} \approx \mathbf{0.0833} 6 0.50 ​ ≈ 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

Expression Returned Result Explanation

COUNT(*) 0 COUNT always returns a non-negative integer representing the count of rows.

COUNT(salary) 0 Zero non-null values found; returns numeric 0 .

SUM(salary) NULL No non-null values exist to sum; returns NULL (NOT 0).

AVG(salary) NULL Cannot divide by zero count of values; returns NULL .

MIN(salary) NULL No minimum value exists; returns NULL .

MAX(salary) NULL No maximum value exists; returns NULL . 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) .