6.2 GROUP BY Clause & Aggregation Rules

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.2 GROUP BY Clause & Aggregation Rules

Key Takeaways Explain This Topic AI

The GROUP BY clause partitions table rows into discrete summary groups based on identical values in the specified grouping expressions.

The Golden Rule of Grouping (ORA-00979): Any column or non-aggregate expression in the SELECT list must be explicitly listed in the GROUP BY clause.

The Single-Group Rule (ORA-00937): If a query contains aggregate functions in the SELECT list without a GROUP BY clause, individual unaggregated columns cannot appear in the SELECT list.

Column aliases cannot be used in the GROUP BY clause because GROUP BY executes before the SELECT clause assigns aliases.

The WHERE clause filters individual rows before grouping occurs; group functions are strictly forbidden in the WHERE clause (ORA-00934).

Last updated: August 2026

6.2 GROUP BY Clause & Aggregation Rules

While applying group functions across an entire table produces a single summary value, practical business reporting often requires breaking down aggregated metrics by department, job role, geographical region, or fiscal quarter. In Oracle SQL, the GROUP BY clause divides the rows of a table into smaller subsets (groups) sharing common values, allowing aggregate functions to calculate summary metrics for each distinct group independently.

The GROUP BY clause is governed by strict structural and syntactic rules. Violating these rules results in well-known Oracle compilation errors such as ORA-00979 and ORA-00937 , which are heavily tested on the 1Z0-071 examination.

• Purpose, Syntax, and Grouping Architecture
• The fundamental syntax of a query using GROUP BY is: SELECT column_or_expression, aggregate_function(column)
• FROM table_name
• [WHERE row_condition]
• GROUP BY column_or_expression

[ORDER BY sort_expression]; +-----------------------------------------------------------------------------------+

• | GROUP BY EXECUTION FLOW |
• | |
• | 1. BASE TABLE ROWS 2. WHERE FILTER 3. GROUP BY BUCKETS |
• | +-------------+------+ +-------------+------+ +----------------------------+ |
• | | Dept | Job | Sal | | Dept | Job | Sal | | Group: Dept 10 | |
• | |------+------+------| |------+------+------| | - Row 1: Sal 5000 | |
• | | 10 | CLK | 5000 | | 10 | CLK | 5000 | | - Row 2: Sal 7000 | |
• | | 10 | MGR | 7000 | | 10 | MGR | 7000 | | => AVG(Sal) = 6000 | |
• | | 20 | CLK | 4000 | ->| 20 | CLK | 4000 | ->+----------------------------+ |
• | | 20 | MGR | 9000 | | 20 | MGR | 9000 | | Group: Dept 20 | |
• | | 30 | CLK | 3000 | | (Row filtered out) | | - Row 3: Sal 4000 | |
• | +-------------+------+ +-------------+------+ | - Row 4: Sal 9000 | |
• | | => AVG(Sal) = 6500 | |
• | +----------------------------+ |
• +-----------------------------------------------------------------------------------+

When Oracle executes a GROUP BY statement:

It applies the WHERE clause to filter out unqualified base rows.

It sorts or hashes the remaining rows into distinct buckets based on the values in the GROUP BY column(s).

It evaluates the group functions independently for each bucket.

It returns one summary row per unique group.

• Single-Column vs. Multi-Column Grouping
• 1. Single-Column Grouping

When grouping by a single column, Oracle creates one output row for every distinct value found in that column. Rows containing NULL in the grouping column form their own single summary group. SELECT department_id, COUNT(*) AS head_count, AVG(salary) AS avg_sal

• FROM employees
• GROUP BY department_id
• ORDER BY department_id;
• 2. Multi-Column (Hierarchical) Grouping

Grouping by multiple columns creates hierarchical groups. Oracle forms a group for every unique combination of values across all listed grouping columns. SELECT department_id, job_id, COUNT(*) AS emp_count, SUM(salary) AS total_payroll

• FROM employees
• GROUP BY department_id, job_id

ORDER BY department_id, job_id; MULTI-COLUMN GROUPING RESULT SET (Sample):

• +---------------+------------+-----------+---------------+
• | DEPARTMENT_ID | JOB_ID | EMP_COUNT | TOTAL_PAYROLL |
• +---------------+------------+-----------+---------------+
• | 10 | AD_ASST | 1 | 4400.00 |
• | 20 | MK_MAN | 1 | 13000.00 |
• | 20 | MK_REP | 1 | 6000.00 |
• | 50 | SH_CLERK | 20 | 64300.00 |
• | 50 | ST_CLERK | 20 | 55700.00 |
• | 50 | ST_MAN | 5 | 36000.00 |
• | 80 | SA_MAN | 5 | 61000.00 |
• | 80 | SA_REP | 29 | 243500.00 |
• +---------------+------------+-----------+---------------+

Exam Tip: The order of columns in the GROUP BY clause ( GROUP BY department_id, job_id vs. GROUP BY job_id, department_id ) does not change group membership or aggregate calculation values. Both produce the exact same summary groups, although output sorting may differ unless specified by ORDER BY .

The Golden Rules of Grouping on 1Z0-071

Two critical aggregation rules govern all SQL SELECT statements in Oracle. Misunderstanding these rules is the most frequent source of errors on the certification exam.

Rule 1: The Group By Inclusion Rule ( ORA-00979 )

The Golden Rule: Every individual column or non-aggregate expression that appears in the SELECT list MUST be explicitly included in the GROUP BY clause.

Why does this rule exist? If a query groups by department_id (returning one row per department) but attempts to select last_name without an aggregate function, Oracle cannot know which employee's last_name to display for a department containing 45 employees. To prevent ambiguous output, Oracle raises ORA-00979 . -- INVALID QUERY: Causes ORA-00979: not a GROUP BY expression

• SELECT department_id, job_id, AVG(salary)
• FROM employees
• GROUP BY department_id;
• - Error: JOB_ID is in the SELECT list but omitted from GROUP BY

-- CORRECTED QUERY:

• SELECT department_id, job_id, AVG(salary)
• FROM employees
• GROUP BY department_id, job_id;
• The Inverse Rule: SELECT Independence

Does every column in the GROUP BY clause have to be in the SELECT list? NO! You can group by columns that are not displayed in the SELECT clause: -- FULLY VALID: Grouping by department_id without selecting it

• SELECT AVG(salary), MAX(salary)
• FROM employees
• GROUP BY department_id;
• Rule 2: Single-Group Aggregation Rule ( ORA-00937 )

Single-Group Rule: If a query contains aggregate functions in the SELECT list but no GROUP BY clause , the entire table is treated as a single summary group. Therefore, no individual (unaggregated) column may appear in the SELECT list. -- INVALID QUERY: Causes ORA-00937: not a single-group group function

• SELECT department_id, AVG(salary)
• FROM employees;
• - Error: Mixing unaggregated DEPARTMENT_ID with table-wide aggregate AVG(salary)

-- SOLUTION 1 (Add GROUP BY clause to produce multi-row groups):

• SELECT department_id, AVG(salary)
• FROM employees
• GROUP BY department_id;

-- SOLUTION 2 (Remove unaggregated column for table-wide summary):

• SELECT AVG(salary)
• FROM employees;
• Grouping by Expressions vs. Column Aliases

In Oracle SQL, you can group by complex expressions, function calls, and datatype conversions, provided the exact expression used in the SELECT list appears in the GROUP BY clause.

• 1. Valid Expression Grouping -- Grouping by year of hire using TO_CHAR
• SELECT TO_CHAR(hire_date, 'YYYY') AS hire_year, COUNT(*), AVG(salary)
• FROM employees
• GROUP BY TO_CHAR(hire_date, 'YYYY')
• ORDER BY hire_year;
• 2. Invalid Use of Column Aliases in GROUP BY ( ORA-00904 )

You cannot use column aliases defined in the SELECT clause within the GROUP BY clause: -- INVALID QUERY: Causes ORA-00904: "HIRE_YEAR": invalid identifier

• SELECT TO_CHAR(hire_date, 'YYYY') AS hire_year, COUNT(*)
• FROM employees
• GROUP BY hire_year; -- ILLEGAL: Aliases cannot be referenced in GROUP BY

Why is this illegal? SQL logical processing order evaluates GROUP BY before the SELECT clause. When the database engine groups rows, the alias hire_year has not yet been instantiated.

Row Filtering with the WHERE Clause Prior to Grouping

The WHERE clause filters individual records before they are partitioned into groups. Understanding the relationship between WHERE and GROUP BY involves two key principles:

Columns used in the WHERE clause do not need to appear in the GROUP BY clause or SELECT list.

Aggregate functions can never be placed inside the WHERE clause ( ORA-00934: group function is not allowed here ). -- Valid query: Filtering rows before grouping

• SELECT department_id, AVG(salary) AS avg_sal
• FROM employees
• WHERE hire_date >= DATE '2005-01-01' -- Evaluated per row before grouping
• AND salary > 3000 -- Column does not need to be in GROUP BY
• GROUP BY department_id
• ORDER BY department_id;
• Common Aggregation Errors Reference Table

Oracle Error Code Error Message Root Cause How to Fix

ORA-00979 not a GROUP BY expression An unaggregated column in SELECT is missing from GROUP BY . Add the column/expression to GROUP BY or wrap it in a group function.

ORA-00937 not a single-group group function An unaggregated column appears with group functions without GROUP BY . Add a GROUP BY clause or remove the unaggregated column.

ORA-00934 group function is not allowed here An aggregate function was used in WHERE , JOIN ON , or GROUP BY . Move group function filters to the HAVING clause.

ORA-00904 "ALIAS": invalid identifier A SELECT column alias was referenced in GROUP BY or WHERE . Repeat the full expression in GROUP BY rather than the alias.