All Clauses
GROUP BY Clause
SELECT Statement
WHERE Clause
CLAUSE: HAVING
SELECT fieldlist FROM table WHERE selectcriteria
GROUP BY groupfieldlist [HAVING groupcriteria]
The
HAVING
clause is optional and qualifies a
GROUP BY
clause. It is similar to the
WHERE
clause, but
HAVING
establishes restrictions that determine which records are displayed after they have been grouped. The following example displays a list of different items, along with their count, but only where there are more than one:
SELECT Item, Count(Item) AS Tally FROM Products
GROUP BY Item HAVING Count(Item) > 1;
A
HAVING
clause can contain up to 40 expressions linked by logical operators such as
AND
and
OR
:
SELECT Item, Count(Item) AS Tally, Max(UnitPrice) AS MaxPrice
FROM Products GROUP BY Item
HAVING Count(Item) > 1 AND Max(UnitPrice) < 40;
Note that the above example returns a list of items only where the whole group meets the criteria of the
HAVING
clause, that is only items of which there are more than 1, and none of which cost more than $40. If you wanted a count of only those items that cost less than $40 (there could be others that cost more) and which number more than 1, then you would have to use the following query:
SELECT Item, Count(Item) AS Tally, Max(UnitPrice) AS MaxPrice
FROM Products WHERE UnitPrice < 40
GROUP BY Item HAVING Count(Item) > 1;
Copyright 1999-2001 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information