HAVING Clause
Filtering Grouped Data in SQL

Introduction

Once you’ve grouped your data using GROUP BY, you often want to apply conditions on those grouped results — for example, finding classes where the average score is above 90. The HAVING clause makes that possible. Think of it as a WHERE for groups.

Syntax of HAVING Clause

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

Sample Table – student_marks

We’ll use this student marks table:

CREATE TABLE student_marks (
  roll_no INT,
  name VARCHAR(50),
  class VARCHAR(10),
  subject VARCHAR(30),
  marks INT
);

INSERT INTO student_marks VALUES
(1, 'Aarav Sharma', '10A', 'Maths', 85),
(2, 'Diya Iyer', '9B', 'Maths', 90),
(3, 'Rohit Menon', '10A', 'Maths', 78),
(4, 'Sneha Patil', '8C', 'Maths', 88),
(5, 'Mehul Agarwal', '9B', 'Maths', 92),
(1, 'Aarav Sharma', '10A', 'Science', 91),
(2, 'Diya Iyer', '9B', 'Science', 87),
(3, 'Rohit Menon', '10A', 'Science', 85),
(4, 'Sneha Patil', '8C', 'Science', 89),
(5, 'Mehul Agarwal', '9B', 'Science', 93);

1. GROUP BY with HAVING – Basics

Let’s find subjects with average marks above 88:

SELECT subject, AVG(marks) AS avg_marks
FROM student_marks
GROUP BY subject
HAVING AVG(marks) > 88;

subject | avg_marks
--------+-----------
Science | 89.0

2. Compare HAVING vs WHERE

WHERE filters rows before grouping. HAVING filters after grouping.

Example: Only consider 'Maths' subject, then filter classes by their average:

SELECT class, AVG(marks) AS avg_maths
FROM student_marks
WHERE subject = 'Maths'
GROUP BY class
HAVING AVG(marks) > 85;

class | avg_maths
------+-----------
9B    | 91.0
8C    | 88.0

3. HAVING with COUNT

Let’s find classes that have more than 2 subject entries:

SELECT class, COUNT(*) AS entry_count
FROM student_marks
GROUP BY class
HAVING COUNT(*) > 2;

4. HAVING with SUM

Find students whose total marks (across subjects) exceed 175:

SELECT name, SUM(marks) AS total_score
FROM student_marks
GROUP BY name
HAVING SUM(marks) > 175;

name            | total_score
----------------+-------------
Mehul Agarwal  | 185
Aarav Sharma   | 176

5. HAVING with Multiple Conditions

Find subject-wise average where marks are greater than 85 and entry count is more than 3:

SELECT subject, AVG(marks) AS avg_marks, COUNT(*) AS total_entries
FROM student_marks
GROUP BY subject
HAVING AVG(marks) > 85 AND COUNT(*) > 3;

Best Practices

• Use HAVING only with GROUP BY — or when using aggregate functions.
• Don’t confuse HAVING with WHERE — WHERE filters raw data, HAVING filters groups.
• Alias your aggregated columns for better readability and filtering.

Summary

The HAVING clause is what you reach for when WHERE isn’t enough. When you want to filter based on totals, averages, counts — after you’ve grouped the data — HAVING makes it possible.

What’s Next?

Now that you’ve mastered filtering grouped data, let’s move on to the LIMIT clause — which helps you fetch just the top results, such as the top 3 scorers per class or subject.