Oracle SQL GROUP BY and HAVING: The 1Z0-071 Rules Most Candidates Get Wrong

1Z0-071 Exam Prep Chapter 6 Volume 2

GROUP BY and HAVING questions on the 1Z0-071 exam are not just about knowing the syntax. The exam targets the rules — what you can and cannot put in the SELECT list when GROUP BY is present, where aggregate functions are and are not allowed, and exactly when to use HAVING instead of WHERE.

Getting one rule wrong here costs you marks across several questions because the same rules apply consistently throughout the topic.

In this post

1. How GROUP BY works
2. The SELECT list rule — the most tested constraint
3. Grouping on multiple columns
4. WHERE vs HAVING — which filters where
5. Aggregate functions — COUNT, SUM, AVG, MAX, MIN
6. Nesting aggregate functions
7. Query execution order — why it matters
8. Quick reference
9. Practice questions

01 — How GROUP BY works

GROUP BY collapses multiple rows that share the same value in the grouping column into a single summary row. Once rows are grouped, you can apply aggregate functions to each group separately.

-- Without GROUP BY: one aggregate result for the entire table
SELECT COUNT(*), AVG(salary)
FROM   employees;
-- Returns: 1 row — total count and average across all employees

-- With GROUP BY: one result row per department
SELECT   department_id, COUNT(*), AVG(salary)
FROM     employees
GROUP BY department_id;
-- Returns: one row per unique department_id value
-- Each row shows the count and average salary for that department

The mental model: Think of GROUP BY as sorting the rows into buckets — one bucket per unique value in the GROUP BY column. Aggregate functions then summarise each bucket into a single output row.

02 — The SELECT list rule — the most tested constraint

This is the rule the exam tests more than any other in this topic. When you use GROUP BY, every column in the SELECT list must be either:

• Listed in the GROUP BY clause, or
• Wrapped inside an aggregate function

Any column that is neither will raise an error.

-- WRONG: last_name is in SELECT but not in GROUP BY and not aggregated
SELECT   department_id, last_name, COUNT(*)
FROM     employees
GROUP BY department_id;
-- Error: ORA-00979: not a GROUP BY expression

-- CORRECT: last_name added to GROUP BY
SELECT   department_id, last_name, COUNT(*)
FROM     employees
GROUP BY department_id, last_name;

-- CORRECT: last_name removed from SELECT, only grouped column + aggregate
SELECT   department_id, COUNT(*)
FROM     employees
GROUP BY department_id;

Exam trap: The rule goes only one way. You can GROUP BY a column without putting it in the SELECT list — that is valid. But you cannot put a non-aggregated column in the SELECT list without it being in GROUP BY. The exam presents both directions to see if you know the difference.

-- Valid: GROUP BY a column that is NOT in the SELECT list
SELECT   COUNT(*), AVG(salary)
FROM     employees
GROUP BY department_id;
-- department_id not in SELECT — that is fine

03 — Grouping on multiple columns

You can GROUP BY more than one column. Oracle creates one output row for each unique combination of values across all the grouping columns.

-- One row per unique (department_id, job_id) combination
SELECT   department_id,
         job_id,
         COUNT(*) AS headcount,
         SUM(salary) AS total_salary
FROM     employees
GROUP BY department_id, job_id
ORDER BY department_id, job_id;
-- If dept 50 has 3 different job_ids, it contributes 3 rows to the result

Order does not affect grouping: GROUP BY department_id, job_id and GROUP BY job_id, department_id produce the same groups and the same result rows — just potentially in a different order if no ORDER BY is specified.

04 — WHERE vs HAVING — which filters where

This distinction is one of the most frequently tested points in the entire GROUP BY topic.

• WHERE filters individual rows before they are grouped
• HAVING filters groups after they have been formed

-- WHERE filters rows before grouping
-- Only rows where salary > 5000 are included in the groups
SELECT   department_id, COUNT(*), AVG(salary)
FROM     employees
WHERE    salary > 5000
GROUP BY department_id;

-- HAVING filters groups after grouping
-- Groups are formed from all rows, then groups with avg < 8000 are removed
SELECT   department_id, COUNT(*), AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   AVG(salary) > 8000;

-- Both together: WHERE reduces rows first, HAVING filters the resulting groups
SELECT   department_id, COUNT(*), AVG(salary)
FROM     employees
WHERE    job_id != 'SA_REP'
GROUP BY department_id
HAVING   COUNT(*) > 2;

Cannot use aggregate functions in WHERE: WHERE AVG(salary) > 8000 raises ORA-00934. Aggregate functions are not allowed in the WHERE clause — they belong in HAVING. The exam regularly presents this as a wrong answer option.

	WHERE	HAVING
Filters	Individual rows	Groups
Runs	Before GROUP BY	After GROUP BY
Aggregate functions allowed?	No — raises ORA-00934	Yes
Can reference GROUP BY column?	Yes	Yes
Required when using GROUP BY?	No	No — both are optional

05 — Aggregate functions — COUNT, SUM, AVG, MAX, MIN

All five aggregate functions are tested on the exam. The key behaviour to know for each is how they handle NULL values and what happens when the group has no rows.

SELECT   department_id,
         COUNT(*)                AS total_rows,
         COUNT(commission_pct)   AS with_commission,
         SUM(salary)             AS salary_total,
         AVG(salary)             AS salary_avg,
         MAX(salary)             AS highest,
         MIN(salary)             AS lowest
FROM     employees
GROUP BY department_id;

Function	NULL rows	Empty group result	Exam catch
COUNT(*)	Counted	0	Different from COUNT(col) when NULLs exist
COUNT(col)	Skipped	0	Returns fewer than COUNT(*) when col has NULLs
SUM(col)	Skipped	NULL	Returns NULL (not 0) for an empty group
AVG(col)	Skipped from sum and count	NULL	Divides by non-null count, not total rows
MAX(col)	Skipped	NULL	NULL is never the maximum
MIN(col)	Skipped	NULL	NULL is never the minimum

DISTINCT inside aggregates: You can use DISTINCT to count or sum only unique values. COUNT(DISTINCT job_id) counts how many different job types exist in the group — not how many employees. The exam tests this occasionally.

06 — Nesting aggregate functions

Oracle allows one level of aggregate nesting — an aggregate function applied to the result of another aggregate function. This is only valid when GROUP BY is present.

-- Find the highest average salary across all departments
SELECT   MAX(AVG(salary))
FROM     employees
GROUP BY department_id;
-- AVG(salary) calculates an average per department
-- MAX() then finds the largest of those averages
-- Returns: a single value — the highest department average

-- WRONG: nested aggregate without GROUP BY
SELECT MAX(AVG(salary)) FROM employees;
-- Error: ORA-00978: nested group function without GROUP BY

Nesting rule: When you nest aggregate functions, you cannot include any other columns or expressions in the SELECT list alongside them. The outer aggregate must stand alone.

07 — Query execution order — why it matters

Understanding the order Oracle processes each clause explains why certain things are allowed in some clauses but not others. The exam tests this implicitly through questions about what is and is not valid in each clause.

Step	Clause	What happens
1	FROM	Tables are identified and joined
2	WHERE	Individual rows are filtered — no aggregates yet
3	GROUP BY	Remaining rows are grouped
4	HAVING	Groups are filtered — aggregates are available here
5	SELECT	Columns and expressions are evaluated
6	ORDER BY	Results are sorted — column aliases available here

Why you cannot use a SELECT alias in WHERE or HAVING: The SELECT clause runs at step 5 — after WHERE (step 2) and HAVING (step 4). The alias does not exist yet when those clauses are evaluated. This is why WHERE annual_sal > 50000 fails if annual_sal is an alias defined in the SELECT list.

08 — Quick reference

Rule	Valid?	Notes
Non-aggregated column in SELECT, not in GROUP BY	No — ORA-00979	Every SELECT column must be in GROUP BY or aggregated
GROUP BY column not in SELECT	Yes	You can group by a column without selecting it
Aggregate function in WHERE	No — ORA-00934	Use HAVING instead
Aggregate function in HAVING	Yes	This is what HAVING is for
Column alias in HAVING	No	SELECT runs after HAVING — alias not yet defined
Column alias in ORDER BY	Yes	ORDER BY runs last — alias is available
Nested aggregate without GROUP BY	No — ORA-00978	GROUP BY required for nesting
WHERE and HAVING in same query	Yes	WHERE filters rows, HAVING filters groups — both can coexist

09 — Practice questions

Q1 — Which of these queries raises an error?

A) SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5; B) SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; C) SELECT department_id, last_name, COUNT(*) FROM employees GROUP BY department_id; D) SELECT COUNT(*), AVG(salary) FROM employees GROUP BY department_id;

Q2 — A query needs to return only departments where the average salary exceeds 10,000. Which clause should be used to apply this filter?

A) WHERE AVG(salary) > 10000 B) HAVING AVG(salary) > 10000 C) WHERE salary > 10000 combined with GROUP BY D) Either WHERE or HAVING — both work the same way here

Q3 — What does this query return?

SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;

A) The average salary of all employees B) The maximum salary in each department C) A single value — the highest average salary across all departments D) An error — aggregate functions cannot be nested

Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set

19 chapters  ·  1,480 practice questions  ·  1,249 SQL examples
Chapter 6 covers every GROUP BY and HAVING scenario on the exam with full practice sets for each rule. Instant PDF download.

Get the full guide — $25 →

Published by https://www.apps-dba.com — Oracle DBA and certification resources.