HAVING Clause

Specifies groups of rows that appear in the logical table (a recordset) defined by a SELECT statement. The search condition applies to columns specified in a GROUP BY clause, to columns created by aggregate functions, or to expressions containing aggregate functions. If a group doesn't pass the search condition, the database does not include it in the logical table.

Syntax

Notes: If you do not include a GROUP BY clause, the select list must be formed by using one or more of the SQL aggregate functions.

The difference between the HAVING clause and the WHERE clause is that WHERE <searchcondition> applies to single rows before they are grouped, while HAVING <search-condition> applies to groups of rows.

If you include a GROUP BY clause preceding the HAVING clause, the <search-condition> applies to each of the groups formed by equal values in the specified columns. If you do not include a GROUP BY clause, the <search-condition> applies to the entire logical table defined by the SELECT statement.

Example

To find the invoice amounts for all invoices that total more than $150, enter the following (qxmplTotalInvoices>150):

SELECT tblCompanies.CompanyName, tblInvoices.InvoiceID,
  tblInvoices.InvoiceDate, Sum(tblContactProducts.SoldPrice) AS InvoiceTotal
 FROM (tblCompanies
  INNER JOIN tblInvoices
  ON tblCompanies.CompanyID = tblInvoices.CompanyID)
  INNER JOIN tblContactProducts
  ON tblInvoices.InvoiceID = tblContactProducts.InvoiceID
 GROUP BY tblCompanies.CompanyName, tblInvoices.InvoiceID,
  tblInvoices.InvoiceDate
HAVING Sum(tblContactProducts.SoldPrice) > 150;