Back to AGGREGATIONS & GROUP BY, ORDER BY

HAVING CLAUSE AND WHERE

Understand filtering with GROUP BY and difference between HAVING and WHERE

4 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

WHERE Aggregate Error — Attempting to filter by an aggregate function in the WHERE clause results in an error because the aggregation has not yet occurred.
HAVING Clause Purpose — The HAVING clause was created specifically to filter results based on aggregate functions after the GROUP BY clause executes.
Basic HAVING Example — A successful query filters groups where the average age is greater than 40 using HAVING.
WHERE Row Filtering — The WHERE clause is used first to filter individual rows, such as selecting only occupations that are managers.
Combined Filtering Logic — Both WHERE and HAVING are used in one query to filter rows first, then filter the resulting aggregated groups.
Summary and Distinction — WHERE filters row data before grouping, while HAVING filters aggregated results after grouping.

PDF notes

Bottom line: You will be able to correctly apply filtering logic in SQL queries that use aggregation. You will know when to use the WHERE clause for row-level filtering and when to use the HAVING clause for filtering aggregated results. This ensures your queries execute efficiently and without errors related to aggregate function usage.

This lesson on HAVING CLAUSE AND WHERE is hands-on and example-driven. You will be able to correctly apply filtering logic in SQL queries that use aggregation. You will know when to use the WHERE clause for row-level filtering and when to use the HAVING clause for filtering aggregated results. This ensures your queries execute efficiently and without errors related to aggregate function usage.

What You'll Be Able To Do

Differentiate between row-level and group-level filtering in SQL.
Construct queries that correctly use aggregate functions in the HAVING clause.
Explain why aggregate functions cannot be used directly in the WHERE clause.
Combine WHERE and HAVING clauses effectively within a single query.
Identify the correct execution order of WHERE, GROUP BY, and HAVING.

Topics Covered in HAVING CLAUSE AND WHERE

WHERE Aggregate Error (0:00 - 0:55) — Attempting to filter by an aggregate function in the WHERE clause results in an error because the aggregation has not yet occurred.
HAVING Clause Purpose (0:55 - 1:45) — The HAVING clause was created specifically to filter results based on aggregate functions after the GROUP BY clause executes.
Basic HAVING Example (1:45 - 2:05) — A successful query filters groups where the average age is greater than 40 using HAVING.
WHERE Row Filtering (2:05 - 3:15) — The WHERE clause is used first to filter individual rows, such as selecting only occupations that are managers.
Combined Filtering Logic (3:15 - 3:50) — Both WHERE and HAVING are used in one query to filter rows first, then filter the resulting aggregated groups.
Summary and Distinction (3:50 - 4:15) — WHERE filters row data before grouping, while HAVING filters aggregated results after grouping.

SQL Cheat Sheet

WHERE <condition> — Filters individual rows before grouping or aggregation occurs
```
SELECT * FROM salary WHERE occupation LIKE '%manager%';
```
GROUP BY <col> — Groups rows based on identical values in specified columns
```
SELECT gender, AVG(age) FROM demographics GROUP BY gender;
```

HAVING <aggregate condition> — Filters groups based on the results of aggregate functions

SELECT gender, AVG(age) FROM demographics GROUP BY gender HAVING AVG(age) > 40;

WHERE ... GROUP BY ... HAVING ... — Filters rows first, then groups, then filters the resulting groups

SELECT occupation, AVG(salary) FROM salary WHERE occupation LIKE '%manager%' GROUP BY occupation HAVING AVG(salary) > 75000;

Comparison Table

Clause	Filters	Execution Order	Function Usage
WHERE	Individual rows	Before GROUP BY	Cannot use aggregates
HAVING	Groups or aggregates	After GROUP BY	Must use aggregates
Both	Rows then groups	WHERE -> GROUP BY -> HAVING	Filters at two levels

Common Pitfalls

Mistake: Using an aggregate function like AVG() in the WHERE clause. Avoid: Use HAVING instead of WHERE when filtering based on aggregate results.
Mistake: Placing HAVING before the GROUP BY clause in the query. Avoid: HAVING must always come immediately after the GROUP BY clause.
Mistake: Trying to filter non-aggregated columns using HAVING. Avoid: Use WHERE for filtering non-aggregated columns at the row level.

FAQs

Why does using an aggregate function in WHERE cause an error? The aggregate function result has not been calculated yet because the grouping (GROUP BY) has not occurred when WHERE executes.
Can I use both WHERE and HAVING in the same query? Yes, WHERE filters the initial rows, and HAVING filters the resulting groups after aggregation.
Does HAVING replace WHERE? No, they serve different purposes; WHERE filters rows, and HAVING filters groups based on aggregate results.

Common pitfalls

Mistake: Using an aggregate function like AVG() in the WHERE clause. Avoid: Use HAVING instead of WHERE when filtering based on aggregate results.
Mistake: Placing HAVING before the GROUP BY clause in the query. Avoid: HAVING must always come immediately after the GROUP BY clause.
Mistake: Trying to filter non-aggregated columns using HAVING. Avoid: Use WHERE for filtering non-aggregated columns at the row level.

Hello everybody. In this lesson, we're going to take a look at the difference between having and where. Now, in the last lesson, we looked at group by and order by. The most obvious thing to do would be to come right here and say where and we're going to say this column, which is actually named this. We'll say where the average age, let's say is greater than 40, which would only be the males. So let's go ahead and run this. And as you can see, we're not getting any output. Let's bring this up and take a look at the error. It says invalid use of the group by function. What's actually happening is something to do with this group by gender right here. When we're selecting gender and then we're performing an aggregate function, this occurs only after the group by actually groups those rows together. So when we're trying to filter based off of this column right here of average age, it really hasn't been created yet because this group by hasn't happened. That's where the having clause comes into play. So let's go ahead and what we're going to do is we're going to get rid of this. We're going to come right down here and instead of where, we're going to say having. Now, having was specifically created for this exact example. It comes right after group by and after group by, we can filter based off of these aggregate functions. So now if we run this, we're going to get an output that only has where the average age is greater than 40. Now let's take a look at just one more example and I'm going to show you how you can use both in one query. So instead of demographics, let's look at the salary table. And let's run it. Now, in the salary table, we have this occupation and remember, we have this office manager that happens twice. And this is going to be our main example. So we're going to say occupation and then we'll say the average salary. Now we'll need to come down here, and we'll say group by. Now we're going to say occupation. So this should look pretty similar because right here we have our office manager. And one of the office managers made 50, one of the office managers made 60, so the average is 55,000. Now, I can use the where by saying where and I'll say occupation like and let's see people who are managers. So I'll say a percent manager percent and close that quote. So they're like a manager. And then I want to see where a manager makes more than let's say 75,000. So I won't actually say where. I'm going to say having an average salary, and I need to add a space there, having an average salary greater than, let's say, 75,000. And let's run this. So now I filtered at the row level right here in the where clause, but then down here I filtered at the aggregate function level. This having is only going to work for aggregated functions after the group by actually runs. So that is the difference between the having clause and the where clause. The where clause you're most likely going to use a lot more, but if you do want to filter on those aggregated function columns, you have to use the having clause. I hope that that was really helpful. And in the next lesson, we're looking at our very last lesson in our beginner series, we're going to look at limit and aliasing.

Frequently asked questions

Why does using an aggregate function in WHERE cause an error?

The aggregate function result has not been calculated yet because the grouping (GROUP BY) has not occurred when WHERE executes.

Can I use both WHERE and HAVING in the same query?

Yes, WHERE filters the initial rows, and HAVING filters the resulting groups after aggregation.

Does HAVING replace WHERE?

No, they serve different purposes; WHERE filters rows, and HAVING filters groups based on aggregate results.