In some instances, you might want to exclude individual rows from groups (using a WHERE clause) before applying a condition to groups as a whole (using a HAVING clause).
A HAVING clause is like a WHERE clause, but applies only to groups as a whole (that is, to the rows in the result set representing groups), whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. In that case:
For example, imagine that you are joining the titles
and publishers
tables to create a query showing the average book price for a set of publishers. You want to see the average price for only a specific set of publishers — perhaps only the publishers in the state of California. And even then, you want to see the average price only if it is over $10.00.
You can establish the first condition by including a WHERE clause, which discards any publishers that are not in California, before calculating average prices. The second condition requires a HAVING clause, because the condition is based on the results of grouping and summarizing the data. The resulting SQL statement might look like this:
SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(price) > 10
You can create both HAVING and WHERE clauses in the Grid pane of the Query Designer. By default, if you specify a search condition for a column, the condition becomes part of the HAVING clause. However, you can change the condition to be a WHERE clause.
You can create a WHERE clause and HAVING clause involving the same column. To do so, you must add the column twice to the Grid pane, then specify one instance as part of the HAVING clause and the other instance as part of the WHERE clause.
To specify a WHERE condition in an aggregate query
Note The query shown in the illustrations for this procedure joins two tables, titles
and publishers
.
At this point in the query, the SQL statement contains a HAVING clause:
SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
GROUP BY titles.pub_id
HAVING publishers.state = 'CA'
The SQL statement changes to include a WHERE clause instead:
SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id