SQL HAVING Clause

The HAVING clause is used to filter groups created by the GROUP BY clause. It works similarly to the WHERE clause, but with one important difference: WHERE filters individual rows before grouping, while HAVING filters the entire group after aggregation has taken place.

Think of it this way: WHERE decides which students enter the room. GROUP BY arranges them into city groups. HAVING then decides which city groups are shown in the final report.

The Reference Table

Syntax

SELECT column_name, aggregate_function(column)
FROM table_name
GROUP BY column_name
HAVING condition;

Example 1: Cities With More Than 1 Student

SELECT City, COUNT(*) AS TotalStudents
FROM Students
GROUP BY City
HAVING COUNT(*) > 1;

Result:

Chennai and Kolkata each have only 1 student, so they are excluded by the HAVING filter.

Example 2: Courses Where Total Fees Exceed 90000

SELECT Course, SUM(Fees) AS TotalFees
FROM Students
GROUP BY Course
HAVING SUM(Fees) > 90000;

Result:

Arts has only 42000 in total fees, which is below the threshold, so it is excluded.

Example 3: Cities Where the Average Fee is Above 45000

SELECT City, AVG(Fees) AS AvgFee
FROM Students
GROUP BY City
HAVING AVG(Fees) > 45000;

Result:

WHERE vs HAVING — The Key Difference

Using WHERE and HAVING Together

Both clauses can be used in the same query. WHERE filters rows first, then GROUP BY groups the remaining rows, and finally HAVING filters the groups.

SELECT City, COUNT(*) AS TotalStudents
FROM Students
WHERE Course = 'Science'
GROUP BY City
HAVING COUNT(*) >= 1;

Step-by-step:

1. WHERE Course = 'Science' — keeps only Science students (rows 1, 3, 6, 7).
2. GROUP BY City — groups them: Delhi (2), Chennai (1), Mumbai (1).
3. HAVING COUNT(*) >= 1 — all groups pass (all have at least 1 student).

HAVING With ORDER BY

SELECT City, COUNT(*) AS TotalStudents
FROM Students
GROUP BY City
HAVING COUNT(*) > 1
ORDER BY TotalStudents DESC;

This shows only cities with more than one student, sorted from most to fewest.

Key Points to Remember

• HAVING is used to filter groups, not individual rows.
• HAVING must come after GROUP BY in the query.
• Aggregate functions like COUNT(), SUM(), and AVG() can be used in HAVING but not in WHERE.
• Use WHERE to filter rows before grouping and HAVING to filter groups after aggregation.

Query Clause Order

Summary

The HAVING clause is the filter for grouped data. It works alongside GROUP BY to show only those groups that satisfy a specific aggregate condition — such as cities with more than 2 students or courses with total fees above a threshold. Understanding the difference between WHERE (row-level filtering) and HAVING (group-level filtering) is a key milestone in mastering SQL.