Using Subqueries in HAVING to Filter Aggregated Data

Sometimes we need to not just group data and filter the result, but do it with some extra logic — like, say, compare the average grade of students in a group with some external criteria. This is where HAVING with subqueries comes in — a powerful tool that helps you make smarter decisions right inside your SQL query.

Remembering HAVING

Let's focus on subqueries used together with HAVING to filter data at the aggregated level. Why? If WHERE lets you filter individual rows, HAVING is applied to already grouped data — it's a different level of analysis that expands your possibilities.

Before we dive into combining subqueries and HAVING, let's refresh what HAVING is and how it's different from WHERE.

• WHERE filters rows before grouping (GROUP BY).
• HAVING filters data after aggregation, when the data is already grouped.

Imagine you're analyzing students and their grades. With WHERE you can exclude students with certain minimum grades, but HAVING lets you exclude whole groups of students based on their average or max score.

Sample Data

Here's a table with some student examples:

Table students:

Example of HAVING (without subqueries)

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) >= 75;

Result:

The "History" department didn't make it into the selection because its average grade is below 75. Pretty simple, right? Now let's add a little magic with subqueries. In the next example, we can, for instance, filter by comparing with the overall average across all departments.

Subqueries in HAVING

Subqueries in HAVING are a great way to add flexibility when filtering aggregated data. They let you compare aggregates, like average or max grade, with calculated values from other parts of the database. In plain English, you can check: "Is our result better than the hospital average?"

Example: Filtering Departments by Average Grade

Let's say we want to find those departments where students are doing better than the rest — that is, the department's average grade is higher than the university average.

Here's our data:

Table students:

First, let's get the average grade for all students:

SELECT AVG(grade) AS university_avg
FROM students;

Now let's use a subquery in HAVING:

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > (SELECT AVG(grade) FROM students);

Result:

What's happening here?

1. The subquery (SELECT AVG(grade) FROM students) calculates the overall average grade — in this case, it's 77.
2. The main query groups students by department and calculates the average grade for each.
3. HAVING compares the department's average grade with the overall average and only lets through those departments where the result is higher.

Comparing WHERE and HAVING

To get the difference, imagine you want to select only those students who have grades above average. You can do this only with WHERE:

SELECT name, grade
FROM students
WHERE grade > (SELECT AVG(grade) FROM students);

Result (using the table from previous examples):

But if you want to see which departments have an average student grade above the university average, you can't do it without HAVING — because you're filtering not rows, but groups:

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > (SELECT AVG(grade) FROM students);

Result:

In short:

• WHERE works with individual rows before grouping.
• HAVING filters groups after they've been aggregated.

Example: Working with Multiple Aggregates

Let's look at another case. Suppose we have a students table that stores data about student grades and their departments:

Table students:

Now we want to find departments where:

1. The average student grade is higher than the university average.
2. The max grade in the department is over 90.

Here's the query:

SELECT department, AVG(grade) AS avg_grade, MAX(grade) AS max_grade
FROM students
GROUP BY department
HAVING AVG(grade) > ( SELECT AVG(grade) FROM students )
   AND MAX(grade) > 90;

What's happening in this query:

• AVG(grade) > (SELECT AVG(grade) FROM students) — checks that the department is above average.
• MAX(grade) > 90 — means there's someone who aced the exam.

Result:

The "Math" department turned out to be the only one with both an above-average grade and a standout student with a grade over 90.

Example: Selecting Groups with Minimal Deviation

Suppose you want to find groups where the difference between the max and min student grade is less than the difference for the university as a whole.

Here's the students table we'll use:

Let's break the task into steps:

1. First, calculate the max-min difference for the whole university:

   SELECT MAX(grade) - MIN(grade) AS range_university
   FROM students;
   

2. Now let's make the main query and join it with this subquery:

SELECT department, MAX(grade) - MIN(grade) AS range_department
FROM students
GROUP BY department
HAVING (MAX(grade) - MIN(grade)) < ( SELECT MAX(grade) - MIN(grade) FROM students );

Result of the query:

The "Physics" and "Math" groups showed more stable grades — their spread is less than the university as a whole.

Optimizing Queries with HAVING and Subqueries

Keep in mind that nested subqueries can seriously affect performance, especially in large databases. Here are a few tips:

Use indexes. If your subquery runs on a column that's used in WHERE or JOIN, make sure that column is indexed.

Avoid data overflow. If your subquery returns too many intermediate results, break it into steps or use temp tables.

Profile queries with EXPLAIN. Always check how PostgreSQL runs your query. If you see the subquery running multiple times, think about optimizing it.

Compare with CTE. In some cases, using WITH (Common Table Expressions) can be faster and easier to read. But more on that in the next lectures :P

Combining Subqueries, HAVING and GROUP BY

With subqueries in HAVING you can build more complex filters, especially when you need to consider aggregates, averages, and other metrics at the same time. All this helps you find cool insights in real data.

Example: Comparing Departments by Average Grade and Student Count

Suppose you want to select departments where:

1. The average grade is above the university average.
2. The number of students is greater than in the department with the lowest average grade.

Here's the original students table:

Query:

SELECT department, AVG(grade) AS avg_grade, COUNT(*) AS student_count
FROM students
GROUP BY department
HAVING AVG(grade) > ( SELECT AVG(grade) FROM students )
   AND COUNT(*) > (
       SELECT COUNT(*)
       FROM students
       GROUP BY department
       ORDER BY AVG(grade)
       LIMIT 1
   );

This query shows how you can combine subqueries in HAVING and GROUP BY to analyze by several criteria at once. Result:

The History department didn't make the cut because it has the lowest average grade and the fewest students. Physics and Math — both above average in grades and headcount.

Common Mistakes and How to Avoid Them

NULL mistake. If your data has NULLs, subqueries with HAVING can return unexpected results. Use COALESCE to handle these cases:

SELECT AVG(grade)
FROM students 
WHERE grade IS NOT NULL;

Too much data in the subquery. If your subquery returns too much, it'll hurt performance. Always make your subquery conditions as specific as possible.

Misunderstanding execution order. Remember, HAVING runs after grouping, but subqueries might run before the main query.

Missing indexes. If columns used in the subquery aren't indexed, your query will be much slower.

Subqueries in HAVING open up a ton of possibilities for analyzing data at the aggregate level. You can filter groups by complex conditions, compare results between groups, and build advanced analytical queries. Congrats, now you're ready to use this knowledge in real projects!