SQL HAVING Clause — Filtering Grouped Data

The office wants only classes with at least two students. The required condition depends on a group count.

HAVING filters grouped results, while WHERE filters individual rows before grouping.

First establish the input rows, then form class groups, compute each count and apply HAVING COUNT(*) >= 2. If the school first limits the input to a city using WHERE, the count describes that city subset rather than the entire class.

Related concepts: Group and Summarize and Filter with WHERE.

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.

SQL · portable concepts; preserved examples identify their engine

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.

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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.

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.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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.

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.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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.

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.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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.

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.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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.

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.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

• 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.

Moving a condition between WHERE and HAVING can change what is counted. Do not compare an ungrouped individual mark as if it represented an entire group. Some engines allow aliases in HAVING; using the aggregate expression is more portable.

Return only classes whose student count is at least two.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT class, COUNT(*) AS students FROM practice_students GROUP BY class HAVING COUNT(*) >= 2 ORDER BY class;

The class 9B group has count one and is excluded. Class 10A remains because its count meets the inclusive boundary.

HAVING filters grouped results, while WHERE filters individual rows before grouping.

The class 9B group has count one and is excluded. Class 10A remains because its count meets the inclusive boundary.

Continue your understanding with Group and Summarize and Filter with WHERE.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.