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

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

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

© 2004 Microsoft Corporation. All rights reserved.