MySQL HAVING

The HAVING clause is used to filter groups after they have been created using GROUP BY.

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

This returns only courses that have more than five students.

HAVING is useful when you need to filter the result of an aggregate calculation.

• Courses with more than 10 students
• Students with total payments above ₹10,000
• Departments with an average salary above a specific amount
• Products with total sales above a specific amount
• Months with collection above a target

The basic syntax is:

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

Example:

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

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

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

Only courses containing more than 10 students will be displayed.

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 whose total fee is greater than 50,000.

HAVING can filter groups according to their average value.

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

This returns courses whose average marks are at least 70.

HAVING can be used with MAX() to filter groups based on their highest value.

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

This returns courses where at least one student's marks are 90 or higher.

HAVING can also filter groups according to their minimum value.

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

This returns courses where the lowest marks in the group are at least 40.

In MySQL, a SELECT alias can generally be referenced in the HAVING clause.

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

The alias total_students represents the COUNT(*) result.

WHERE and HAVING can be used in the same query.

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

Here:

• WHERE selects active students.
• GROUP BY creates course groups.
• HAVING keeps groups containing more than five active students.

You can combine multiple conditions using AND.

SELECT
    course,
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee
FROM students
GROUP BY course
HAVING COUNT(*) > 5
AND SUM(fee) > 50000;

Both conditions must be satisfied.

OR can be used when either condition should be true.

SELECT
    course,
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee
FROM students
GROUP BY course
HAVING COUNT(*) > 10
OR SUM(fee) > 100000;

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

HAVING supports comparison operators such as:

• >
• <
• >=
• <=
• =
• <>

Example:

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

COUNT(DISTINCT) can be used inside HAVING.

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

This returns cities that have students from more than two different courses.

HAVING can filter grouped date results.

SELECT
    YEAR(payment_date) AS payment_year,
    COUNT(*) AS payment_count
FROM payments
GROUP BY YEAR(payment_date)
HAVING COUNT(*) > 100;

This returns years having more than 100 payment records.

You can create a month-wise collection report and filter the months using HAVING.

SELECT
    YEAR(payment_date) AS payment_year,
    MONTH(payment_date) AS payment_month,
    SUM(amount) AS total_collection
FROM payments
GROUP BY
    YEAR(payment_date),
    MONTH(payment_date)
HAVING SUM(amount) > 50000
ORDER BY payment_year, payment_month;

Only months with collections above 50,000 are displayed.

You can filter groups and then sort the remaining results.

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

The qualifying courses are displayed from highest total fee to lowest.

A HAVING condition can use multiple aggregate expressions.

SELECT
    course,
    COUNT(*) AS total_students,
    ROUND(AVG(marks), 2) AS average_marks,
    MAX(marks) AS highest_marks
FROM results
GROUP BY course
HAVING COUNT(*) > 5
AND AVG(marks) >= 60;

This filters courses based on both student count and average marks.

HAVING can filter groups created from multiple columns.

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

This returns course-and-city groups containing more than three students.

HAVING can also be used without an explicit GROUP BY when the query produces a single aggregate group.

SELECT
    COUNT(*) AS total_students
FROM students
HAVING COUNT(*) > 100;

If the total number of students is greater than 100, the row is returned. Otherwise, no row is returned.

You can use an expression inside an aggregate function and filter it with HAVING.

SELECT
    course,
    SUM(total_fee - paid_fee) AS total_due
FROM students
GROUP BY course
HAVING SUM(total_fee - paid_fee) > 10000;

This displays courses where the total outstanding amount is greater than 10,000.

HAVING can identify students whose total payments exceed a specific amount.

SELECT
    student_id,
    SUM(amount) AS total_paid
FROM payments
GROUP BY student_id
HAVING SUM(amount) > 10000;

This returns students whose total recorded payments exceed 10,000.

HAVING can be used to identify courses with a required average performance.

SELECT
    course,
    COUNT(*) AS total_students,
    ROUND(AVG(marks), 2) AS average_marks
FROM results
GROUP BY course
HAVING AVG(marks) >= 70;

Only courses with an average of at least 70 are displayed.

HAVING can be used with joined tables and grouped results.

SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
GROUP BY c.course_name
HAVING COUNT(s.student_id) > 5;

This returns courses having more than five enrolled students.

Suppose you want courses having more than five active students.

Correct approach:

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

WHERE first selects active students, then GROUP BY creates course groups, and HAVING filters those groups.

A common mistake is trying to use an aggregate condition directly in WHERE.

Incorrect:

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

Correct:

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

A simplified logical order of a grouped query is:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Example:

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

SELECT
    course,
    COUNT(*) AS total_students,
    SUM(total_fee) AS total_fee,
    SUM(paid_fee) AS total_paid,
    SUM(total_fee - paid_fee) AS total_due
FROM students
GROUP BY course
HAVING SUM(total_fee - paid_fee) > 10000
ORDER BY total_due DESC;

This report displays courses where the total outstanding fee is greater than 10,000.

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

INSERT INTO students
(name, course, city, fee, paid_fee, marks, status)
VALUES
('Amit Kumar', 'Python', 'Patna', 15000, 12000, 85, 'Active'),
('Priya Singh', 'Java', 'Patna', 18000, 18000, 92, 'Active'),
('Rahul Sharma', 'Python', 'Gaya', 12000, 7000, 78, 'Active'),
('Neha Kumari', 'PHP', 'Patna', 10000, 5000, 88, 'Inactive'),
('Ravi Kumar', 'Python', 'Gaya', 15000, 9000, 81, 'Active'),
('Pooja Singh', 'Java', 'Patna', 16000, 10000, 75, 'Active'),
('Suman Devi', 'Python', 'Patna', 14000, 8000, 90, 'Active');

-- Courses with more than two students
SELECT
    course,
    COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING COUNT(*) > 2;

-- Courses with total fee above 40000
SELECT
    course,
    SUM(fee) AS total_fee
FROM students
GROUP BY course
HAVING SUM(fee) > 40000;

-- Courses with average marks above 80
SELECT
    course,
    ROUND(AVG(marks), 2) AS average_marks
FROM students
GROUP BY course
HAVING AVG(marks) > 80;

-- Active students with course-wise totals
SELECT
    course,
    COUNT(*) AS active_students,
    SUM(paid_fee) AS total_paid
FROM students
WHERE status = 'Active'
GROUP BY course
HAVING SUM(paid_fee) > 15000
ORDER BY total_paid DESC;

-- Course-wise outstanding report
SELECT
    course,
    SUM(fee - paid_fee) AS total_due
FROM students
GROUP BY course
HAVING SUM(fee - paid_fee) > 5000
ORDER BY total_due DESC;

This example demonstrates HAVING with COUNT(), SUM(), AVG(), WHERE, GROUP BY, and ORDER BY to create practical reports.