Oracle SQL GROUP BY and HAVING

Oracle SQL GROUP BY and HAVING: Complete Guide

The GROUP BY clause lets you summarize data by grouping rows that share the same values in specified columns. The HAVING clause lets you filter those groups based on aggregate results. Together they are essential for creating meaningful summaries and reports from your data.

Learning Objectives
By the end of this guide you will understand how GROUP BY works, know exactly what can and cannot appear in the SELECT list, master the difference between WHERE and HAVING, and learn how to use aggregate functions correctly.

1. How GROUP BY Works

GROUP BY collapses multiple rows that share the same value in the grouping column(s) into a single summary row. Aggregate functions are then applied to each group.

-- Without GROUP BY: one result for the entire table
SELECT COUNT(*), AVG(salary)
FROM   employees;

-- With GROUP BY: one result row per department
SELECT department_id, COUNT(*), AVG(salary)
FROM   employees
GROUP BY department_id;

2. The SELECT List Rule

When using GROUP BY, every column in the SELECT list must be either:

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

-- Correct
SELECT department_id, COUNT(*), AVG(salary)
FROM   employees
GROUP BY department_id;

-- Incorrect — last_name is neither grouped nor aggregated
SELECT department_id, last_name, COUNT(*)
FROM   employees
GROUP BY department_id;

Common mistake: You can GROUP BY a column without selecting it, but you cannot select a non-aggregated column without including it in the GROUP BY clause.

3. Grouping on Multiple Columns

You can group by more than one column. Each unique combination of values creates a separate group.

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;

4. WHERE vs HAVING

	WHERE	HAVING
Filters	Individual rows	Groups after grouping
Runs	Before GROUP BY	After GROUP BY
Aggregate functions allowed?	No	Yes

-- WHERE filters rows before grouping
SELECT department_id, COUNT(*), AVG(salary)
FROM   employees
WHERE  salary > 5000
GROUP BY department_id;

-- HAVING filters groups after grouping
SELECT department_id, COUNT(*), AVG(salary)
FROM   employees
GROUP BY department_id
HAVING AVG(salary) > 8000;

5. Aggregate Functions

Function	NULL handling	Empty group result
COUNT(*)	Counts all rows	0
COUNT(col)	Skips NULLs	0
SUM(col), AVG(col), MAX(col), MIN(col)	Skips NULLs	NULL

6. Interactive Practice Quiz

Q1. Which query will raise an error?

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

Q2. You want to show only departments where the average salary is greater than 8000. Which clause should you use?

A. WHERE AVG(salary) > 8000 B. HAVING AVG(salary) > 8000 C. Either WHERE or HAVING works the same D. GROUP BY AVG(salary) > 8000

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 highest salary in each department C. A single value — the highest average salary across all departments D. An error — you cannot nest aggregate functions

Conclusion

GROUP BY and HAVING are powerful tools for summarizing and filtering grouped data. The most important rules to remember are: every non-aggregated column in the SELECT list must appear in the GROUP BY clause, WHERE filters rows before grouping, and HAVING filters groups after grouping.

Practice writing queries that combine grouping, aggregates, and both WHERE and HAVING clauses — these patterns appear frequently in real-world reporting and analysis.