6.3 Filtering Groups with the HAVING Clause

6.3 Filtering Groups with the HAVING Clause

When writing summary reports in SQL, developers frequently need to filter output based on aggregated metrics—such as finding departments with an average salary exceeding $8,000, or identifying product categories with more than 50 orders. Because the WHERE clause filters individual records before rows are grouped, SQL provides the HAVING clause specifically to filter summarized groups after aggregation occurs.

Understanding the precise differences between WHERE and HAVING, their logical execution sequence, and the strict rules governing nested group functions is essential for mastering the Oracle Database SQL (1Z0-071) exam.

---

Purpose and Mechanics of the HAVING Clause

The HAVING clause specifies search conditions for groups rather than individual rows. When a query contains a HAVING clause, Oracle evaluates the group condition against each group formed by the GROUP BY clause. Only groups that satisfy the HAVING predicate are included in the final result.

-- Example: Filtering departments with an average salary greater than 8000
SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000
ORDER BY average_salary DESC;

---

SQL Logical Query Execution Sequence

To understand why WHERE cannot filter on group functions while HAVING can, you must master the Logical Query Processing Lifecycle of an Oracle SQL statement:

+-----------------------------------------------------------------------------------+
|                       SQL LOGICAL EXECUTION LIFECYCLE                             |
|                                                                                   |
|  [1. FROM / JOIN]    -> Identify tables, resolve joins, build raw dataset         |
|         |                                                                         |
|         v                                                                         |
|  [2. WHERE]          -> Filter individual rows (Pre-aggregation filter)           |
|         |                                                                         |
|         v                                                                         |
|  [3. GROUP BY]       -> Partition remaining rows into summary groups              |
|         |                                                                         |
|         v                                                                         |
|  [4. HAVING]         -> Filter summarized groups (Post-aggregation filter)        |
|         |                                                                         |
|         v                                                                         |
|  [5. SELECT]         -> Evaluate expressions, group functions, assign aliases     |
|         |                                                                         |
|         v                                                                         |
|  [6. ORDER BY]       -> Sort the final result set (Aliases & positions valid)     |
+-----------------------------------------------------------------------------------+

Step-by-Step Lifecycle Analysis:

1. FROM & JOIN: Oracle identifies the source tables, views, or inline views and performs Cartesian products or table joins.
2. WHERE: Evaluates single-row predicates. Rows evaluating to FALSE or UNKNOWN are eliminated. Group functions cannot be used here because groups have not yet been formed.
3. GROUP BY: Divides the surviving rows into discrete groups based on common values in the grouping expressions.
4. HAVING: Applies aggregate predicates to each group. Groups evaluating to FALSE or UNKNOWN are dropped.
5. SELECT: Projects the requested columns, computes scalar expressions, calculates final group function values, and assigns column aliases.
6. ORDER BY: Sorts the resulting records. Because ORDER BY executes last, it can reference column aliases and numeric column positions.

---

Side-by-Side Comparison: WHERE vs. HAVING

Combining WHERE and HAVING in a Single Query

SELECT department_id, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date >= DATE '2005-01-01'  -- Filters base rows BEFORE grouping
GROUP BY department_id
HAVING COUNT(*) >= 5                 -- Filters groups AFTER grouping
   AND AVG(salary) > 6000
ORDER BY avg_sal DESC;

In this query:

1. Employees hired before 2005-01-01 are discarded in the WHERE step.
2. The remaining employees are grouped by department_id.
3. The HAVING clause keeps only departments with at least 5 qualifying employees whose average salary exceeds 6000.

---

Syntax Flexibility: Placement of HAVING in Oracle SQL

While ANSI SQL conventions and industry best practices place the HAVING clause immediately after the GROUP BY clause, Oracle SQL syntactically permits the HAVING clause to precede the GROUP BY clause:

-- FULLY VALID IN ORACLE SQL: HAVING before GROUP BY
SELECT department_id, MAX(salary)
FROM employees
HAVING MAX(salary) > 10000
GROUP BY department_id;

Both syntactic arrangements produce identical execution plans and results. Furthermore, Oracle permits a HAVING clause without any GROUP BY clause, treating the entire table as a single group:

-- VALID: HAVING without GROUP BY
SELECT AVG(salary)
FROM employees
HAVING AVG(salary) > 5000;

Exam Tip: If an exam question asks whether placing HAVING before GROUP BY results in a compilation error, remember that in Oracle SQL it is 100% syntactically legal.

---

Restrictions on HAVING Predicates

1. Non-aggregate columns in HAVING: Any column referenced in the HAVING clause that is not enclosed inside a group function must appear in the GROUP BY clause.

-- VALID: department_id is in the GROUP BY clause
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING department_id IN (10, 20, 30);

-- INVALID: job_id is NOT in the GROUP BY clause -> Causes ORA-00979
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING job_id = 'IT_PROG';

1. HAVING Aggregate Independence: Aggregate expressions used in HAVING do not need to appear in the SELECT list:

-- FULLY VALID: MIN(salary) is in HAVING but not in SELECT
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > 2500;

---

Nesting Group Functions

Oracle SQL allows aggregate functions to be nested inside other aggregate functions—for instance, to calculate the maximum department average salary.

-- Calculate the highest average department salary
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;

The Strict Rules of Nested Group Functions on 1Z0-071

+-----------------------------------------------------------------------------------+
|                     NESTED GROUP FUNCTION RULES MATRIX                            |
+---------------------------------------+-------------------------------------------+
| Rule                                  | Specification & Constraint                |
+---------------------------------------+-------------------------------------------+
| Maximum Nesting Depth                 | Exactly TWO levels (e.g., MAX(AVG(col))). |
|                                       | 3 levels raises ORA-00935.                |
+---------------------------------------+-------------------------------------------+
| GROUP BY Requirement                  | A GROUP BY clause is MANDATORY.           |
|                                       | Omission raises ORA-00978.                |
+---------------------------------------+-------------------------------------------+
| SELECT List Restriction               | The SELECT list may ONLY contain the      |
|                                       | nested group function itself. No          |
|                                       | individual columns or un-nested group     |
|                                       | functions are permitted.                  |
+---------------------------------------+-------------------------------------------+

Why Columns Cannot Appear with Nested Group Functions

Consider the query:

-- ILLEGAL: Causes ORA-00937: not a single-group group function
SELECT department_id, MAX(AVG(salary))
FROM employees
GROUP BY department_id;

Analysis:

1. AVG(salary) grouped by department_id produces multiple rows (one average per department).
2. MAX(AVG(salary)) collapses those department averages into a single scalar value across the entire table.
3. Because the outer MAX reduces the result to a single row, Oracle cannot display individual department_id values alongside it without raising ORA-00937.

3-Level Nesting is Prohibited

-- ILLEGAL: Causes ORA-00935: group function is nested too deeply
SELECT SUM(MAX(AVG(salary)))
FROM employees
GROUP BY department_id;