SQL HAVING Clause - Filtering Grouped Data

SQL HAVING Clause

Once you’ve grouped your data using GROUP BY clause, 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;

The HAVING clause is used to filter the results of a GROUP BY query based on aggregate functions. Unlike the WHERE clause, which filters rows before grouping, HAVING filters groups after the aggregation is performed.

• SELECT column1, aggregate_function(column2): Specifies the columns to retrieve, including at least one aggregate function (e.g., SUM, AVG, COUNT).
• FROM table_name: Indicates the source table.
• GROUP BY column1: Groups the rows based on values in column1.
• HAVING condition: Applies a condition to the grouped results, typically involving the aggregate function (e.g., HAVING AVG(column2) > 50).

Key point: Use HAVING to filter groups — not individual rows — especially when the filter depends on the result of an aggregate function.

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);

students

roll_no	name	class	subject	marks
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:

Use case: A school administrator wants to identify subjects where the average student marks exceed 88, to recognize high-performing subjects or departments.

students

roll_no	name	class	subject	marks
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

SQL query description: SQL query to select each subject and calculate the average marks from the student_marks table. The results are grouped by subject, and only those groups with an average greater than 88 are included using the HAVING clause.

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

Result description: Result contains a list of subjects along with their average marks, but only for those subjects where the average marks are greater than 88.

Query Result

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:

Use case: A school administrator wants to identify classes where the average marks in Maths exceed 85 to recognize high-performing groups.

students

roll_no	name	class	subject	marks
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

SQL query description: SQL query to select the class and the average of marks (aliased as avg_maths) from the student_marks table, filtering only rows where subject is 'Maths'. The results are grouped by class and further filtered using HAVING to include only those with an average Maths mark greater than 85.

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

Result description: Result contains the class names and their corresponding average Maths marks, but only for classes where the average exceeds 85.

Query Result

class	avg_maths
8C	88.0
9B	91.0

3. HAVING with COUNT

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

Use case: A school administrator wants to identify classes that have more than two student mark entries in order to prioritize data review or reporting for those classes.

students

roll_no	name	class	subject	marks
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

SQL query description: SQL query to select the class and the count of records (as entry_count) from the student_marks table, grouping the results by class and filtering to include only those groups with more than two entries.

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

Result description: Result contains the class names and the number of student mark entries for each class, limited to classes that have more than two entries.

Query Result

class	entry_count
10A	4
8C	2
9B	4

4. HAVING with SUM

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

Use case: A teacher wants to identify students whose total marks across all subjects exceed 175, in order to recognize high-performing students.

students

roll_no	name	class	subject	marks
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

SQL query description: SQL query to select the name and the total of marks (as total_score) from the student_marks table, grouped by name, and filtered to include only those students whose total marks are greater than 175.

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

Result description: Result contains the names of students and their total scores, but only for those whose combined marks exceed 175.

Query Result

name	total_score
Aarav Sharma	176
Diya Iyer	177
Mehul Agarwal	185
Sneha Patil	177

5. HAVING with Multiple Conditions

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

Use case: A school administrator wants to identify subjects where students are consistently performing well, based on having an average mark greater than 85 and at least 4 recorded entries.

students

roll_no	name	class	subject	marks
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

SQL query description: SQL query to select each subject from the student_marks table along with the average marks (AVG(marks)) and the number of entries (COUNT(*)). The results are grouped by subject and filtered using the HAVING clause to include only those subjects where the average marks are greater than 85 and the number of entries 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;

Result description: Result contains the subject names, their average marks, and total entry counts for subjects where students scored an average above 85 and have more than 3 mark entries.

Query Result

subject	avg_marks	total_entries
Maths	86.6	5
Science	89.0	5

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.