HAVING Clause

Description

Specifies which grouped records are displayed in a SELECT statement with a GROUP BY clause. After GROUP BY combines records, HAVING displays any records grouped by the GROUP BY clause that satisfy the conditions of the HAVING clause.

Syntax

SELECT fieldlist
FROM table
WHERE selectcriteria
GROUP BY groupfieldlist
[HAVING groupcriteria]

A SELECT statement containing a HAVING clause has these parts:

Part	Description

fieldlist	The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table	The name of the table from which records are retrieved. For more information, see the FROM clause.
selectcriteria	Selection criteria. If the statement includes a WHERE clause, the Microsoft Jet database engine groups values after applying the WHERE conditions to the records.
groupfieldlist	The names of up to 10 fields used to group records. The order of the field names in groupfieldlist determines the grouping levels from the highest to the lowest level of grouping.
groupcriteria	An expression that determines which grouped records to display.

Remarks HAVING is optional.

HAVING is similar to WHERE, which determines which records are selected. After records are grouped with GROUP BY, HAVING determines which records are displayed:

SELECT CategoryID,
Sum(UnitsInStock)
FROM Products
GROUP BY CategoryID
HAVING Sum(UnitsInStock) > 100 And Like "BOS*";

A HAVING clause can contain up to 40 expressions linked by logical operators, such as And and Or.

See Also ALL, DISTINCT, DISTINCTROW, TOP predicates, FROM clause, GROUP BY clause, SELECT statement, SELECT...INTO statement, WHERE clause.

Example

This example selects the job titles assigned to more than one employee in the Washington region.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub HavingX()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Select the job titles assigned to more than one 
    ' employee in the Washington region. 
    Set rst = dbs.OpenRecordset("SELECT Title, " _
        & "Count(Title) AS Total FROM Employees " _
        & "WHERE Region = 'WA' " _
        & "GROUP BY Title HAVING Count(Title) > 1;")

    ' Populate the Recordset.
    rst.MoveLast

    ' Call EnumFields to print recordset contents.
    EnumFields rst, 25

    dbs.Close

End Sub

Example (Microsoft Access)

To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

The following example finds the suppliers whose products have an average unit price over $25:

SELECT SupplierID, Avg(UnitPrice) AS AvgOfUnitPrice
FROM Products GROUP BY SupplierID
HAVING (Avg(UnitPrice)>25);

The next example selects employees who have sold more than 100 orders:

SELECT EmployeeID, Count(OrderID) AS CountOfOrderID
FROM Orders GROUP BY EmployeeID
HAVING Count(OrderID) > 100;