WHERE v HAVING
By Richard Rost
16 days ago
How To Use WHERE vs HAVING In Microsoft Access SQL In this video, we will discuss the difference between the WHERE and HAVING clauses in Microsoft Access SQL. I will explain how WHERE filters individual records before grouping, while HAVING filters the grouped results after aggregation. We will walk through beginner and advanced examples, showing how to filter records before and after using aggregate functions, and highlight common mistakes when using these clauses. This lesson is intended for viewers with some basic SQL knowledge and familiarity with aggregate queries in Access. Clara from Baltimore, Maryland (a Platinum Member) asks: How do I tell the difference between WHERE and HAVING in Access? I'm trying to build a query for work and I can filter my data fine at first, but once I group things together I get stuck and I'm not sure when to use one or the other. They both seem to do the same thing and it's confusing me. MembersThere is no extended cut, but here is the file download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, HAVING vs WHERE, differences between HAVING and WHERE, GROUP BY, aggregate query, COUNT function, AVG function, filter grouped results, SQL order of operations, query criteria, CustomerID, FamilySize, State, SELECT statement, aggregate functions, SQL for Access users
Subscribe to WHERE v HAVING
Get notifications when this page is updated
Intro In this video, we will discuss the difference between the WHERE and HAVING clauses in Microsoft Access SQL. I will explain how WHERE filters individual records before grouping, while HAVING filters the grouped results after aggregation. We will walk through beginner and advanced examples, showing how to filter records before and after using aggregate functions, and highlight common mistakes when using these clauses. This lesson is intended for viewers with some basic SQL knowledge and familiarity with aggregate queries in Access.Transcript Ever see the word HAVING in a query and think, wait, how is that different from WHERE? Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Today we're going to talk about what the HAVING keyword actually does in Microsoft Access SQL and how it's different from WHERE. If you've ever been confused about why your total query isn't filtering the way you expect, I'm going to show you what's going on and how to fix it.
Today's question comes from Clara in Baltimore, Maryland, one of my Platinum members. She says, "How do I tell the difference between WHERE and HAVING in Access? I'm trying to build a query for work, and I can filter my data fine at first, but once I group things together I get stuck, and I'm not sure when to use one or the other. They both seem to do the same thing, and it's confusing me."
Clara, you're not alone. When I was learning SQL way back in the 90s, HAVING kind of confused me too. So let's talk about it.
But first, some prerequisites. Now, this is an expert level class, so that to me means intermediate. It's between beginner and developer, so we don't need any VBA for today's class, but you should know some basic SQL. If you're new to SQL, go watch this video. It will teach you the basics of using the SQL language inside of Access.
One thing that a lot of people get confused about is that there's the SQL language and then there are programs like SQL Server, which is a whole different thing. This is just the SQL language inside of Access. Go watch this, it'll explain it more.
And you should know what aggregate queries are, also called group queries or total queries. They have a lot of different names. Watch this, it'll explain those in more detail too. These are free videos. They're on my YouTube channel and they're on my website. Go watch those and then come on back.
Alright, so here's a sample SELECT statement that's got both a WHERE and a HAVING clause in it. We're going to get to this one in detail a little bit later in the video. This shows an example of how you can have both a WHERE clause and a HAVING clause in your aggregate query.
What do the two different things mean? They both look very similar and they both filter data, but they do two very different jobs. WHERE filters individual records before grouping. HAVING filters grouped results after grouping.
Another way to think of it is WHERE filters rows or records and HAVING filters groups. So it's all about when the filtering takes place.
Let's see some examples. I'm going to start out simple, and then we're going to get to that complicated one at the end. Hopefully, somewhere along the line, the light bulb will turn on. That's how it happened to me. I just saw one example once and I just got it.
Now, I'm going to use SQL. I'm going to actually write out SQL because I think some things are easier to understand when you can read the SQL. I think this actually gets more complicated when you look at it in the Access visual query designer, the QBE grid, whatever they call it. You'll see what I mean in a few minutes.
We're going to use my TechHelp free template. I have my basic customer table with fields like CustomerID, FirstName, LastName, State, FamilySize, and a few others.
For this example, we're going to focus mainly on State and FamilySize.
Let's start with a simple WHERE condition. Suppose I want to see all the customers with a FamilySize greater than three. Pretty straightforward. There it is. I'm going to copy and paste this. This is just PowerPoint, we're just going to copy this and switch over to Access.
Here's my TechHelp free template. You can grab a free copy of this off my website if you want to. I'm just going to go to Create and then Query Design. We're going to switch over to SQL View, right-click SQL View, and we're just going to paste that command right in there. Same thing. We're going to run it and there we go. There are all the customers with the FamilySize greater than three. Pretty straightforward, very simple. We'll take a look at each one in the query grid too.
This one looks pretty straightforward. There's the fields I selected, got some sorting in there, and there's FamilySize greater than three. This one's easy.
Let's take a look at the next one.
Next, we're going to create a basic aggregate query. Get rid of the WHERE and we're going to say SELECT State, and I want the COUNT of CustomerID. In other words, count the total number of customers. I like to count autonumber fields because you're guaranteed that each record is going to have an autonumber.
We're going to call that CustomerCount. That's an alias. So this COUNT of CustomerID is going to be called CustomerCount. From the customer table, group by the State, and then order by the State.
Let's copy this, switch back over here, go back into SQL, and paste right over that one. Then let's run it. There we go. There are all of our states. There's our CustomerCount. This means I have six total customers from Florida.
If I open up the customer table and I come over here and I find State and I right-click and choose equals Florida, I should get six of them. See if it changes? No.
So that's what that does. Basic customer count. Count of how many customers are in each state.
The important thing is this is no longer looking at individual customer records. Now we're grouping records together and getting this. Each row represents a group of records.
From this result set, let's say I only want to see the states that have more than two customers. So I'm no longer filtering individual records. I'm filtering the group results. That's where HAVING comes into play.
You could save this query as it is, call it Query1, and then make a second query and read in those values and apply your criteria in the second query. That's one option, and there's nothing wrong with that. I do that all the time.
But if you want to learn a little more advanced SQL, you can do it in the same query if you understand that HAVING now works on this result. So now we're going to take this and we're going to add a HAVING line there. Same thing, but now we're saying HAVING that COUNT of CustomerID is greater than two.
We'll take this, copy it. What it's going to do is get rid of all these ones here, basically.
Go back into here, SQL View, and we're just going to paste that in. Now with that HAVING, look at that. We apply the criteria to the grouped result. That's what HAVING does.
So once again, WHERE filters rows. HAVING filters groups. WHERE filters the individual records before the grouping. HAVING filters the grouped results after grouping.
Here's a mistake a lot of people make. They try to do a WHERE in place of where they should have a HAVING. If you do this, copy, come back over here and paste, you'll get an error. You can't have an aggregate function in a WHERE clause. That's what HAVING is for.
Remember the SQL order of operations. You know how in math you have that order of operations, PEMDAS: parentheses and exponents, then multiplication and division go together left to right; it's not multiplication then division. You see these memes online that everybody gets wrong. In fact, I did a whole Captain's Log article about this a while back.
But there's an SQL order of operations too. I memorized this: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. I have to come up with an acronym for that. Let's think about it.
Well, we can't come up with an acronym. You guys know an acronym is something that can actually be said like a word. Like RADAR is an acronym. FBI is not an acronym because you don't say FIB. That's an initialism. This would more importantly be a mnemonic. So maybe like "Silly frogs wear green hats often" or something like that. I just memorized it.
It's like back in school, you always had to memorize the planets, like "Mary's velvet eyes." I just remember the order of the planets because I'm a space nerd. Mercury, Venus, Earth, etc.
Anyways, the order of operations. Someone's beaming in. You have your SELECT, which tells it what fields. Then Access figures out what table you're working with or tables. Then it applies the WHERE to filter out the rows that you want. Then it groups those rows. Then it applies HAVING to the grouped results, and then it sorts everything. That order matters.
Let's do another example using AVERAGE instead of COUNT. Let's say I want to group the customers by state and only show states where the average FamilySize is greater than two.
So SELECT State, AVG(FamilySize) as AverageFamilySize from customer table, GROUP BY State, HAVING the average FamilySize greater than two, and then ORDER BY State. We'll copy that.
See how this is easier than looking at the query grid sometimes? You'll see in the last one.
Run it, and there we go. The average FamilySize is greater than two. People from New York are doing with a quarter kid. I don't know.
We group by state and then of that result set, I want the ones where that average value is greater than two.
Now let's put them all together. This is the one I showed you up at the top. Now this makes a lot more sense, doesn't it? We're going to select state, the count of customer IDs or the count of the customers from that state, the average FamilySize from CustomerT, WHERE the FamilySize is greater than two. So before we do any grouping, we're getting rid of any customers that have a FamilySize of one or two or zero. Those people are just gone before we do any grouping.
Then we group by state. Then we calculate the average FamilySize and only show the ones where that FamilySize is greater than three. Then we order by the FamilySize descending, and then if those are equal, we order by state.
Copy that, switch over to here, SQL View, and we'll paste it in and there we go. There are our results. We're finding the customers whose FamilySize is greater than two, right? FamilySize is greater than two. Then from those customers, group by state, and then only show the states where the average FamilySize is still greater than three.
Here's our fields, there's our table, filter the raw customer records so we're only working with the customers whose FamilySize is greater than two. Now group what's left by state. Now filter the grouped results so we only see states where the average FamilySize in that group set is greater than three, and then do your sorting.
This prevents you from having to make two, maybe three other queries. You can just do it all in one. Query one could filter the FamilySize. Query two could then group by state, and then Query three would filter the group results again.
When I was first learning SQL, and in fact, in some of my beginner and lower-level expert classes, that is how I teach it because it's easier to teach beginners to make multiple queries. This HAVING stuff can be difficult for beginners to wrap their heads around sometimes, so I don't even cover this until, oh, let's find out. Yeah, I make a brief discussion of it in Access Expert 18. You've already had a bunch of classes by that point, and then we cover it seriously in my SQL Seminar Part Two.
If you want to learn more SQL, I have tons of SQL on my website. I have my new SQL for Access Users course. This is actually SQL Server. I have my SQL Seminars that teach you all the different basics of working with SQL inside of Microsoft Access. Here's putting your Access database online with SQL Server. I have lots of SQL stuff on my website, so check it out. And a really cool logo.
So the big takeaway here again: WHERE filters your records before grouping. HAVING filters your results after grouping. Remember those two things and you'll use each one in the right place.
Now post a comment down below. Let me know how you liked today's video and if you knew the difference between WHERE and HAVING before watching this video. Is this going to make things easier for you or not? Are you more confused? Are you less confused? Did this help? I want to hear about it.
But either way, that's going to be your TechHelp video for today, brought to you by AccessLearningZone.com. I hope you learned something. Live long and prosper my friends. I'll see you next time.Quiz Q1. What is the primary difference between WHERE and HAVING in SQL?
A. WHERE filters records before grouping, HAVING filters after grouping
B. WHERE filters after grouping, HAVING filters before grouping
C. Both WHERE and HAVING filter after grouping
D. Both WHERE and HAVING filter before grouping
Q2. In Microsoft Access SQL, what does the HAVING clause filter?
A. Individual records
B. Groups of records (aggregate results)
C. Field names
D. Table names
Q3. Which clause should be used to filter individual rows before applying GROUP BY?
A. WHERE
B. HAVING
C. SELECT
D. ORDER BY
Q4. What error will you encounter if you place an aggregate function in a WHERE clause?
A. No error, it will work as expected
B. The query will return no rows
C. The query will produce a syntax error
D. The query will sum the function incorrectly
Q5. In the example given, what does COUNT(CustomerID) as CustomerCount do?
A. Counts the number of customers in each group
B. Calculates the sum of CustomerID
C. Filters records based on CustomerID
D. Finds the highest CustomerID in each group
Q6. What is the order of operations in a typical Access SQL statement as described in the video?
A. FROM, SELECT, WHERE, HAVING, GROUP BY, ORDER BY
B. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
C. WHERE, SELECT, GROUP BY, HAVING, ORDER BY, FROM
D. SELECT, GROUP BY, FROM, HAVING, WHERE, ORDER BY
Q7. Which clause is used to filter the grouped results after the GROUP BY operation?
A. WHERE
B. JOIN
C. HAVING
D. UNION
Q8. If you want to filter states with more than two customers after grouping by state, which clause should you use?
A. WHERE COUNT(CustomerID) > 2
B. HAVING COUNT(CustomerID) > 2
C. ORDER BY COUNT(CustomerID) > 2
D. GROUP BY COUNT(CustomerID) > 2
Q9. Which SQL clause is responsible for sorting the final query results?
A. SELECT
B. HAVING
C. ORDER BY
D. GROUP BY
Q10. According to the video, why might using multiple queries be easier for beginners?
A. HAVING clauses are only available in advanced SQL
B. It avoids HAVING, which can be difficult to understand at first
C. Multiple queries are always faster to execute
D. The WHERE clause is always preferred over HAVING
Answers: 1-A; 2-B; 3-A; 4-C; 5-A; 6-B; 7-C; 8-B; 9-C; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on understanding the difference between WHERE and HAVING in Microsoft Access SQL. Many people have seen the HAVING keyword in a query and wondered how it truly differs from WHERE. If you've ever built a totals query and felt confused about your filtering options, you're not alone. I've had similar questions when learning SQL myself. Today, I'm going to clarify what each clause does and when you should use them.
This topic actually comes from a student who had trouble deciding when to use WHERE or HAVING. She could filter data easily at first, but got lost when grouping records together. This is a common challenge, and it's important to understand how both of these keywords work in SQL, especially within Access.
Before going further, keep in mind that this is more of an expert-level lesson, so you should have a basic familiarity with Access queries and some introductory SQL. If you're new to SQL or aggregate queries - also called group queries or total queries - be sure to check out the free tutorials on my website or YouTube channel to get up to speed before coming back to this lesson.
Let's get started by looking at how both clauses function. WHERE and HAVING look similar in a query, and both are used to filter data, but the stage at which they operate is different. WHERE filters individual records before any grouping or aggregate processing occurs. HAVING, on the other hand, filters the results after data has already been grouped or summarized.
To help you understand, here's a simple example. Suppose you have a customer table, and you want to find all customers with a family size greater than three. You would use the WHERE clause to filter out those records before any grouping is performed. This is straightforward and is something most Access users have done before.
Now, let's look at a basic grouped or aggregate query. Imagine you want a list of states along with the number of customers in each. You would group the records by state and count the customer IDs. At this stage, you're not working with individual records but with groups - each row of your result now represents all customers from a given state.
But what if you only want to see states with more than two customers? Now you're trying to filter groups, not individual records. This is where HAVING comes in. While you could use a second query to filter out groups, you can accomplish this directly with a HAVING clause in the same SQL statement, which makes your work more efficient. With HAVING, you apply your condition to the grouped results - filtering by the count of customers in this example.
A common mistake is to try to use an aggregate function like COUNT or AVG in a WHERE clause within an aggregate query, which results in an error. Aggregate functions must be used with HAVING, not WHERE.
It is important to remember the SQL order of operations: SELECT, FROM, WHERE, GROUP BY, HAVING, then ORDER BY. WHERE filters your rows before grouping occurs. GROUP BY forms your groups. HAVING then filters those groups after grouping, and finally ORDER BY sorts the results. I encourage you to find a way to remember this order, as it will help keep your queries logical and error-free.
Let's try another scenario: let's say you want to see the average family size for each state and only display the states where that average is greater than two. Here, you would use HAVING again because you are filtering based on the result of an aggregate function. After grouping by state, HAVING lets you filter the results by the calculated average.
Finally, let's bring together both clauses in a more complex example. If you want all customers whose family size is greater than two, but after grouping them by state, only display those states where the average family size is greater than three, both WHERE and HAVING are required. WHERE filters out customers before grouping, and HAVING then evaluates the grouped results. This eliminates the need for multiple separate queries and enables you to achieve complex filtering in one step.
When teaching SQL to beginners, I often recommend using multiple queries to break down these steps, but once you're comfortable, mastering WHERE and HAVING in the same query will greatly enhance your database skills.
In summary, remember these key points: WHERE filters individual records before grouping, and HAVING filters groups after grouping. Keeping this distinction clear will help you apply the right clause in the right context.
If you found this helpful or have further questions, feel free to leave a comment below. If you'd like a step-by-step walkthrough of everything discussed in this lesson, you can find the complete video tutorial on my website at the link below. Live long and prosper, my friends.Topic List Difference between WHERE and HAVING in Access SQL
Filtering individual records with WHERE
Filtering grouped results with HAVING
Writing basic WHERE conditions in SQL
Creating aggregate (total/group) queries
Using COUNT function in aggregate queries
Applying alias to aggregate results
Grouping records with GROUP BY
Filtering groups with HAVING and aggregate functions
Correct SQL syntax order of operations
Applying WHERE vs HAVING in the proper query stage
Using AVG function with HAVING
Combining WHERE and HAVING in one SQL query
Sorting grouped query results with ORDER BY
Preventing errors using aggregate functions in HAVING
Advanced grouping and filtering in one queryArticle If you have ever come across the HAVING keyword in a query and wondered how it is different from WHERE, you are not alone. Let's talk about exactly what HAVING does in Microsoft Access SQL, how it differs from WHERE, and how to use each one properly. Understanding the difference between WHERE and HAVING is essential if you want your total queries to filter data as you expect.
Both WHERE and HAVING are used to filter data in SQL, but they work at different stages of a query. WHERE filters the individual records before any grouping takes place, while HAVING filters the groups after they have been formed by an aggregate query, such as using GROUP BY. Put simply, WHERE filters rows and HAVING filters groups.
Let's start with a straightforward example using the WHERE clause. Suppose you have a customers table with fields like CustomerID, FirstName, LastName, State, and FamilySize. If you want to see all customers with a FamilySize greater than three, your SQL statement would look like this:
select CustomerID, FirstName, LastName, State, FamilySize
from CustomerT
where FamilySize > 3
order by State, LastName;
This filters the raw records directly, returning only those customers whose FamilySize is greater than three. When you run a query like this in Access, it shows you each record that matches the criteria.
Now let's take it a step further and do some grouping. Suppose you want to see how many customers are in each state. You would write an aggregate query, counting the number of CustomerID values per state:
select State, count(CustomerID) as CustomerCount
from CustomerT
group by State
order by State;
This query groups all your customers by their state and then counts how many are in each group. Each row in the results now represents a group, not an individual record.
Here's where the HAVING clause becomes important. Imagine you only want to see states that have more than two customers. You cannot use WHERE here, because WHERE filters individual records before grouping, and "number of customers per state" does not exist until after grouping. This is when HAVING comes into play, because it lets you filter groups based on aggregate data.
Here is how you would write that query:
select State, count(CustomerID) as CustomerCount
from CustomerT
group by State
having count(CustomerID) > 2
order by State;
Now, after the grouping has occurred, HAVING filters out any states that have two or fewer customers, showing only those groups where the customer count is greater than two.
If you mistakenly try to use WHERE with an aggregate function, like this:
select State, count(CustomerID) as CustomerCount
from CustomerT
where count(CustomerID) > 2
group by State
order by State;
You will get an error, because aggregate functions like COUNT() are not allowed in the WHERE clause. This is exactly why HAVING exists.
You should also keep in mind the SQL order of operations, which helps explain why WHERE and HAVING work the way they do. The basic steps Access follows are: SELECT (what fields you want to see), FROM (what tables those fields are in), WHERE (filter individual records), GROUP BY (form groups), HAVING (filter groups), then ORDER BY (sort the results). WHERE always comes before GROUP BY, while HAVING always comes after.
Let's do another example with a different aggregate function. Suppose you want to see only states where the average family size is greater than two. Your query would look like this:
select State, avg(FamilySize) as AverageFamilySize
from CustomerT
group by State
having avg(FamilySize) > 2
order by State;
This groups the records by state, calculates the average FamilySize for each group, and then shows only those states where the average family size exceeds two.
Now for a more advanced example that puts WHERE and HAVING together in one query. Imagine you want to analyze states, but only for customers whose FamilySize is greater than two, and you want to see only those states where the average family size is greater than three. The SQL would be:
select State, count(CustomerID) as CustomerCount, avg(FamilySize) as AverageFamilySize
from CustomerT
where FamilySize > 2
group by State
having avg(FamilySize) > 3
order by avg(FamilySize) desc, State;
Here, the WHERE clause first filters out any customers whose FamilySize is two or less. The remaining records are grouped by State. The HAVING clause then filters those state groups again, only showing those groups where the average family size within that group is greater than three. The ORDER BY clause is used to sort the results so that states with the largest average family sizes appear first.
This approach lets you accomplish in a single query what would otherwise require several queries chained together. WHERE and HAVING are both filters, but they operate at different stages. WHERE works before the grouping and HAVING works after. Remember this, and you will always know when to use each.
In summary, use WHERE to filter individual records before grouping in your query. Use HAVING to filter groups after an aggregate query has been formed using GROUP BY. If you stick to this rule, it will clear up most confusion about these two very similar-looking, but very different, SQL clauses. This understanding will help you build better, more accurate queries in Access or any SQL-based system. |
