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 2000 by Infinite Software Solutions, Inc.
Trademark Information
|