HAVING Clause in SQL

The HAVING clause is used to filter the results of grouped data.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING COUNT(*) > 5;

This returns only courses having more than 5 students.

HAVING is useful when you want to filter groups based on aggregate calculations.

For example:

• Courses having more than 10 students
• Departments with total salary above ₹100000
• Products with total sales above a certain amount
• Classes having an average mark above 70

The basic syntax is:

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

Example:

SELECT course, COUNT(*)
FROM students
GROUP BY course
HAVING COUNT(*) > 5;

COUNT() is commonly used with HAVING to filter groups based on the number of records.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING COUNT(*) >= 10;

This returns courses with at least 10 students.

HAVING can filter groups according to their total value.

SELECT course, SUM(fee) AS total_fee
FROM students
GROUP BY course
HAVING SUM(fee) > 50000;

This returns courses where the total fee is greater than 50000.

AVG() can be used with HAVING to filter groups according to their average value.

SELECT course, AVG(marks) AS average_marks
FROM students
GROUP BY course
HAVING AVG(marks) >= 70;

This returns courses having an average mark of at least 70.

MIN() can be used with HAVING to filter groups based on their minimum value.

SELECT course, MIN(marks) AS lowest_marks
FROM students
GROUP BY course
HAVING MIN(marks) >= 40;

This returns courses where the minimum marks are at least 40.

MAX() can be used with HAVING to filter groups according to their maximum value.

SELECT course, MAX(marks) AS highest_marks
FROM students
GROUP BY course
HAVING MAX(marks) >= 90;

This returns courses where at least one student has marks of 90 or more.

HAVING is most commonly used after GROUP BY.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING COUNT(*) > 5;

GROUP BY creates the course groups, and HAVING filters those groups.

The main difference is when they filter the data.

• WHERE filters individual rows before grouping.
• HAVING filters groups after grouping.

SELECT course, COUNT(*) AS total_students
FROM students
WHERE status = 'Active'
GROUP BY course
HAVING COUNT(*) > 5;

WHERE is processed before the rows are grouped.

SELECT course, COUNT(*) AS total_students
FROM students
WHERE status = 'Active'
GROUP BY course;

Only active students are included in the groups.

HAVING filters the groups created by GROUP BY.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING COUNT(*) > 5;

Only groups with more than 5 students remain in the result.

WHERE and HAVING can be used together.

SELECT course, COUNT(*) AS total_students
FROM students
WHERE status = 'Active'
GROUP BY course
HAVING COUNT(*) >= 5;

First, inactive students are removed. Then groups with fewer than 5 active students are removed.

Multiple conditions can be combined using AND or OR.

SELECT course, COUNT(*) AS total_students, AVG(marks) AS average_marks
FROM students
GROUP BY course
HAVING COUNT(*) > 5
AND AVG(marks) >= 60;

Both conditions must be satisfied.

The OR operator can be used when either of multiple conditions can be true.

SELECT course, COUNT(*) AS total_students, AVG(marks) AS average_marks
FROM students
GROUP BY course
HAVING COUNT(*) > 10
OR AVG(marks) >= 80;

A group is returned if at least one condition is satisfied.

HAVING can use comparison operators such as >, <, >=, <=, and =.

SELECT course, AVG(marks) AS average_marks
FROM students
GROUP BY course
HAVING AVG(marks) >= 75;

HAVING is very useful for financial reports.

SELECT course, SUM(fee) AS total_fee
FROM students
GROUP BY course
HAVING SUM(fee) >= 100000;

This returns courses whose total fee reaches at least 100000.

You can count unique values and then filter the groups.

SELECT department, COUNT(DISTINCT course) AS course_count
FROM students
GROUP BY department
HAVING COUNT(DISTINCT course) > 2;

This returns departments having more than two different courses.

ORDER BY can be used after HAVING to sort the filtered groups.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING COUNT(*) > 5
ORDER BY total_students DESC;

The qualifying groups are sorted from highest to lowest student count.

In MySQL, an aggregate result alias can commonly be referenced in HAVING.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING total_students > 5;

This makes the query easier to read.

HAVING can filter groups created from multiple columns.

SELECT course, status, COUNT(*) AS total_students
FROM students
GROUP BY course, status
HAVING COUNT(*) > 3;

This filters each course-and-status group.

HAVING can be used to find courses with a minimum number of students.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING COUNT(*) >= 10;

This returns only courses with at least 10 students.

HAVING can filter departments according to their total salary.

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

This returns departments whose combined salary exceeds 100000.

HAVING is useful for finding courses with high payment totals.

SELECT course, SUM(amount) AS total_payment
FROM payments
WHERE status = 'Paid'
GROUP BY course
HAVING SUM(amount) > 50000;

This calculates paid amounts and keeps only courses above 50000.

You can find courses whose average marks meet a required level.

SELECT course, AVG(marks) AS average_marks
FROM students
GROUP BY course
HAVING AVG(marks) >= 70;

This returns courses with an average mark of at least 70.

More than one aggregate condition can be used together.

SELECT course,
       MIN(marks) AS lowest_marks,
       MAX(marks) AS highest_marks
FROM students
GROUP BY course
HAVING MIN(marks) >= 40
AND MAX(marks) >= 90;

This filters courses based on both minimum and maximum marks.

A common mistake is using HAVING when you actually need to filter individual rows.

For example, if you want only active students before calculating groups, use WHERE:

SELECT course, COUNT(*)
FROM students
WHERE status = 'Active'
GROUP BY course;

Use HAVING when filtering the resulting groups.

Consider the following query:

SELECT course, COUNT(*) AS total_students
FROM students
WHERE marks >= 50
GROUP BY course
HAVING COUNT(*) >= 5;

• WHERE keeps students with marks of 50 or more.
• GROUP BY creates groups by course.
• HAVING keeps courses with at least 5 qualifying students.

A practical report can find active courses with at least five students and an average mark of 60 or more.

SELECT
    course,
    COUNT(*) AS total_students,
    AVG(marks) AS average_marks
FROM students
WHERE status = 'Active'
GROUP BY course
HAVING COUNT(*) >= 5
AND AVG(marks) >= 60
ORDER BY average_marks DESC;

This provides a filtered and sorted course-wise report.

Here is a complete example combining WHERE, GROUP BY, HAVING, aggregate functions, and ORDER BY.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100),
    status VARCHAR(20),
    marks INT,
    fee DECIMAL(10,2)
);

SELECT
    course,
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee,
    AVG(marks) AS average_marks,
    MIN(marks) AS lowest_marks,
    MAX(marks) AS highest_marks
FROM students
WHERE status = 'Active'
GROUP BY course
HAVING COUNT(*) >= 5
AND SUM(fee) >= 50000
AND AVG(marks) >= 60
ORDER BY total_students DESC;

This query first filters active students, groups them by course, calculates summary values, keeps only groups satisfying the HAVING conditions, and finally sorts the results.