6.2 GROUP BY Clause & Aggregation Rules

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:

1. It applies the WHERE clause to filter out unqualified base rows.
2. It sorts or hashes the remaining rows into distinct buckets based on the values in the GROUP BY column(s).
3. It evaluates the group functions independently for each bucket.
4. 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:

1. Columns used in the WHERE clause do not need to appear in the GROUP BY clause or SELECT list.
2. 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