DevGuru Jet SQL HAVING Clause

Quick References

Features

Partners


			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;