Filtering Groups

Consider again one of the previous examples, in which we used the COUNT aggregate function to calculate the number of times each page type has been requested:

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, COUNT(*) AS PageTypeHits
FROM ex040528.log 
GROUP BY PageType
ORDER BY PageTypeHits DESC

PageType PageTypeHits
-------- ------------
gif	585
nsf	142
htm	115
jpg	77
exe	25
css	22
js	 11
swf	11
asp	5
class	5
dll	1
html	 1

Let's now assume that we are only interested in seeing page types that have been requested 10 times or more.

At first glance, it might seem that we could use a WHERE clause with a condition on the value of the COUNT aggregate function to filter out the undesired groups.
However, we have seen that the WHERE clause is used to filter input records, which means that this clause is evaluated before groups are created. For this reason, use of aggregate functions is not allowed in the WHERE clause.

The task at hand can be accomplished by using the HAVING clause.
The HAVING clause works just like the WHERE clause, with the only difference being that the HAVING clause is evaluated after groups have been created, which makes it possible for the HAVING clause to specify aggregate functions.

Tip: The HAVING clause must immediately follow the GROUP BY clause.

Using the HAVING clause, we can write the example above as:

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, COUNT(*) AS PageTypeHits
FROM ex040528.log 
GROUP BY PageType
HAVING PageTypeHits >= 10
ORDER BY PageTypeHits DESC

And obtain:

PageType PageTypeHits
-------- ------------
gif	585
nsf	142
htm	115
jpg	77
exe	25
css	22
js	 11
swf	11