In the previous lesson, we learned how to use the GROUP BY clause to group records and calculate results for each group.
Now imagine that a university wants to:
To solve these problems, PostgreSQL provides the HAVING clause.
The HAVING clause is used to filter grouped results, usually after applying aggregate functions.
The HAVING clause filters groups created by the GROUP BY clause.
Syntax:
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
Remember:
WHERE filters individual rows.GROUP BY creates groups.HAVING filters those groups.We will use the students table from the previous lessons.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100),
age INTEGER,
city VARCHAR(50),
fee NUMERIC(10, 2)
);
Insert sample records:
INSERT INTO students
(student_id, student_name, age, city, fee)
VALUES
(1, 'Ali', 20, 'Multan', 15000),
(2, 'Ahmed', 22, 'DG Khan', 18000),
(3, 'Sara', 21, 'Multan', 20000),
(4, 'Ayesha', 23, 'Lahore', 17000),
(5, 'Usman', 20, 'Lahore', 16000),
(6, 'Hassan', 22, 'DG Khan', 19000),
(7, 'Fatima', 21, 'Multan', 18000);
View all records:
SELECT * FROM students;
Suppose we want to display only those cities that have more than two students.
SQL Query:
SELECT
city,
COUNT(*) AS total_students
FROM students
GROUP BY city
HAVING COUNT(*) > 2;
Output:
| city | total_students |
|---|---|
| Multan | 3 |
Explanation:
GROUP BY city groups students by city.COUNT(*) counts students in each group.HAVING COUNT(*) > 2 displays only groups with more than two students.Suppose we want to display only those cities where the total student fee is greater than 40,000.
SQL Query:
SELECT
city,
SUM(fee) AS total_fee
FROM students
GROUP BY city
HAVING SUM(fee) > 40000;
Output:
| city | total_fee |
|---|---|
| Multan | 53000.00 |
Explanation:
SUM(fee) calculates the total fee for each city.HAVING SUM(fee) > 40000 filters the groups according to their total fee.We can use HAVING to filter groups according to their average values.
Example: Display cities where the average student age is greater than 21.
SELECT
city,
AVG(age) AS average_age
FROM students
GROUP BY city
HAVING AVG(age) > 21;
Output:
| city | average_age |
|---|---|
| DG Khan | 22.0 |
| Lahore | 21.5 |
We can also use HAVING with minimum and maximum aggregate functions.
Example 1: Display cities where the minimum student fee is greater than 16,000.
SELECT
city,
MIN(fee) AS minimum_fee
FROM students
GROUP BY city
HAVING MIN(fee) > 16000;
Example 2: Display cities where the maximum student fee is greater than 18,000.
SELECT
city,
MAX(fee) AS maximum_fee
FROM students
GROUP BY city
HAVING MAX(fee) > 18000;
We can use AND and OR operators in the HAVING clause.
Example: Display cities having more than one student AND total fee greater than 35,000.
SELECT
city,
COUNT(*) AS total_students,
SUM(fee) AS total_fee
FROM students
GROUP BY city
HAVING COUNT(*) > 1
AND SUM(fee) > 35000;
Explanation:
Both conditions must be true for a city to appear in the result.
We can use WHERE and HAVING in the same SQL query.
WHERE filters individual records before grouping.HAVING filters groups after grouping.Example:
Display cities having more than one student whose age is greater than 20.
SELECT
city,
COUNT(*) AS total_students
FROM students
WHERE age > 20
GROUP BY city
HAVING COUNT(*) > 1;
How this query works:
WHERE age > 20 selects students older than 20.GROUP BY city groups the selected students by city.COUNT(*) counts students in each city.HAVING COUNT(*) > 1 displays cities with more than one selected student.| WHERE | HAVING |
|---|---|
| Filters individual rows. | Filters groups. |
| Applied before grouping. | Applied after grouping. |
| Commonly used with individual column conditions. | Commonly used with aggregate conditions. |
| Does not directly filter aggregate results. | Can filter aggregate results. |
Example of WHERE:
SELECT *
FROM students
WHERE age > 20;
Example of HAVING:
SELECT city, COUNT(*)
FROM students
GROUP BY city
HAVING COUNT(*) > 2;
Understanding the logical order of SQL clauses helps us understand how WHERE, GROUP BY, and HAVING work together.
A simplified logical processing order is:
1. FROM
Select the table
2. WHERE
Filter individual rows
3. GROUP BY
Create groups
4. HAVING
Filter groups
5. SELECT
Choose output columns
6. ORDER BY
Sort the results
7. LIMIT
Restrict the number of output rows
Use the students table to solve the following exercises.
Beginner Level
Intermediate Level
Advanced Level
HAVING COUNT(*) > 2 mean?| Requirement | SQL Condition |
|---|---|
| More than 2 students | HAVING COUNT(*) > 2 |
| Total fee above 40,000 | HAVING SUM(fee) > 40000 |
| Average age above 21 | HAVING AVG(age) > 21 |
| Minimum fee above 15,000 | HAVING MIN(fee) > 15000 |
| Maximum fee above 18,000 | HAVING MAX(fee) > 18000 |
Basic HAVING pattern:
SELECT
column_name,
COUNT(*) AS total_records
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 2;