Oracle SQL Certification 1Z0-071 Course | Section 10: Reporting Aggregated Data Using Group Functions

Group functions transform many rows into a single result, making them essential for answering questions such as totals, averages, counts, minimums, maximums, and rankings.

In this section of the Oracle SQL 1Z0-071 course, we focus on group functions, also known as aggregate functions. These functions allow you to summarize and analyze data across multiple rows, which is a core skill for reporting, analytics, and certification exams.

This section builds a strong foundation for writing correct aggregate queries and avoiding common mistakes tested in the exam.

Introduction to Group Functions

We begin by defining what group functions are and how they differ from single row functions.

Group functions operate on sets of rows and return a single value per group. Unlike single row functions, they do not return one result per row.

Key characteristics of group functions include:

  • They return one result for multiple rows
  • They ignore NULL values, with specific exceptions
  • They can only be used in SELECT, HAVING, or ORDER BY clauses
  • They cannot appear directly in a WHERE clause

Understanding these rules early helps prevent many syntax and logic errors later in this section.

COUNT and DISTINCT

The COUNT function is one of the most commonly tested aggregate functions in Oracle SQL exams.

In this lesson, you learn:

  • How COUNT(column) ignores NULL values
  • The difference between COUNT(column), COUNT(*), and COUNT(1)
  • Why COUNT never returns NULL
  • How DISTINCT works with COUNT to count unique values

A critical exam concept is that COUNT(*) counts all rows, while COUNT(column) only counts non null values in that column.

SUM, AVG, and MEDIAN

This lesson focuses on numerical aggregation across multiple rows.

You learn how:

  • SUM calculates totals and ignores NULL values
  • AVG divides the sum by the count of non null values
  • MEDIAN returns the middle value after sorting the dataset
  • WHERE can be used to restrict rows before aggregation

You also learn which data types are valid for each function and how filtering affects aggregate results.

MIN and MAX

MIN and MAX are versatile functions that work with numbers, dates, and character data.

In this lesson, you learn:

  • How MIN and MAX behave with numeric values such as salaries
  • How they work with DATE values to find earliest and latest entries
  • How character data is evaluated alphabetically
  • How sorting rules influence results

These functions are simple to use but often tested with tricky data types in exams.

Ranking Functions

Ranking introduces analytical behavior that builds on aggregate concepts.

You learn:

  • How RANK assigns positions based on ordering
  • How duplicate values create skipped ranks
  • How DENSE_RANK avoids gaps in ranking
  • How PARTITION BY allows ranking within groups
  • A technique to rank all rows by partitioning with NULL

Ranking functions are especially important for reporting and exam questions involving ordering and comparison.

GROUP BY Clause

The GROUP BY clause is the backbone of aggregated reporting.

In this lesson, you learn:

  • Why GROUP BY is required when combining aggregates with regular columns
  • How to calculate aggregates per department, job, or manager
  • How to group by multiple columns
  • The rule that every non aggregated column must appear in GROUP BY

Many Oracle SQL exam errors come from incorrect GROUP BY usage, making this lesson essential.

HAVING Clause

HAVING allows you to filter results after aggregation.

You learn:

  • Why WHERE cannot be used with aggregate conditions
  • How HAVING filters grouped results
  • Proper clause order in SQL statements
  • How to filter using aggregate expressions
  • How to apply HAVING to columns not shown in SELECT

Understanding the difference between WHERE and HAVING is frequently tested in the exam.

Nesting Functions

This final lesson shows how to combine multiple functions in a single query.

You learn:

  • How to nest functions when data types are compatible
  • Examples of formatting aggregated values
  • Rules and limits for nesting aggregate functions
  • Why aggregate functions can only be nested two levels deep

This lesson ties together aggregation, formatting, and function execution order.

Exam Focus and Key Takeaways

By the end of this section, you should be comfortable with:

  • Using all major group functions correctly
  • Understanding how NULL values affect aggregation
  • Writing GROUP BY and HAVING clauses without errors
  • Interpreting ranking and analytical results
  • Avoiding common Oracle SQL exam traps

Group functions are a major component of the Oracle SQL 1Z0-071 exam. Practicing these patterns will significantly improve both your exam performance and real world SQL reporting skills.

Now already involved in this SQL journey, you can subscribe to the YouTube channel and maybe subscribe to newsletter below?

Join ForgeFuse Newsletter