Microsoft Access Queries: Difference Between The Where Clause And the Having Clause

Microsoft Access Queries: Difference Between The Where Clause And the Having Clause

Within the framework of using Microsoft Access queries, the HAVING clause can only be used with the GROUP BY type SQL statement which is also referred as the Groups and Totals query. The HAVING keyword when not used with the GROUP BY statement acts as a standard WHERE clause.

The HAVING clause specifies a condition that is similar to the purpose of a WHERE clause when  applying criteria but the two clauses are not interchangeable.

microsoft access queries difference between the where clause and the having clause

The key difference between these two keywords is down to one keyword; Aggregation!

Microsoft Access Queries: Difference Between The Where Clause And the Having Clause

The WHERE clause specifies the criteria which individual records must meet to be selected by the query. It can be used with or without the GROUP BY clause. However, the HAVING clause cannot be used without the GROUP BY clause.

Also, the WHERE clause selects rows before it applies any grouping where as the HAVING keyword selects rows after grouping takes place.

The WHERE clause cannot contain any aggregate functions but the HAVING keyword can contain aggregate functions like Sum, Count, Avg etc.

The following SQL statement will group all customers in the ‘UK’ using the GROUP BY keyword even which will detail individual records due to the unique ID included:

SQL Statement…

SELECT Customers.[Customer ID], Customers.[Company Name], 
Customers.City, Customers.Country 
FROM Customers 
GROUP BY Customers.[Customer ID], Customers.[Company Name], 
Customers.City, Customers.Country 
HAVING   ((Customers.Country)="uk"));

The same effect excluding the GROUP BY clause using the WHERE clause:

SQL Statement…

SELECT Customers.[Customer ID], Customers.[Company Name], 
Customers.City, Customers.Country FROM Customers 
WHERE (((Customers.Country)="uk"));

When using one of the aggregate functions, you can pass criteria too using the HAVING clause only. See example below which only returns a count of customers that exceed 5 hits for each country:

SELECT Count(Customers.[Customer ID]) AS [CountOfCustomer ID], 
Customers.Country FROM Customers  
GROUP BY Customers.Country 
HAVING (((Count(Customers.[Customer ID]))>5));

This wouldn’t work with the WHERE clause as the GROUP BY is needed to first collect and group records then apply an aggregate function (count in this example) and optionally pass a filter; ‘>5′ to it after the grouping of rows or records.

This is a simple example but hopefully the point is made. Another variation of comparing the GROUP BY and WHERE clause can also expand your knowledge.

To learn more about queries, consider one of my eBooks on A Guide to More Access Database Queries which covers different types of queries.

This entry was posted in MS Access, Queries and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.