SQL HAVING Clause

The HAVING clause specifies filtering conditions for a SELECT statement. The syntax is as follows:

  HAVING predicates

Use a HAVING clause to limit the rows retrieved by a SELECT statement to a subset of rows where aggregated field values meet the specified criteria. A HAVING clause can only be used in a SELECT statement when:

• The statement also has a GROUP BY clause.
• One or more fields are the subjects of aggregate functions.

The value for a HAVING clause is one or more logical expressions or predicates that evaluate to true or false for each aggregate line retrieved from the table. Only those rows where the predicates evaluate to true are retrieved by a SELECT statement. For example, the SELECT statement below retrieves all rows where the total Gross Amount for a Customer exceeds $100,000:

  SELECT CustomerCode, SUM(GrossAmount) as TotalGross

  FROM INHEAD

  GROUP BY CustomerCode

  HAVING Sum(GrossAmount) > 100000

  ORDER BY CustomerCode

Multiple predicates must be separated by one of the logical operators OR or AND. Each predicate can be negated with the NOT operator. Parentheses can be used to isolate logical comparisons and groups of comparisons to produce different line evaluation criteria.

A SELECT statement can include both a WHERE clause and a HAVING clause. The WHERE clause filters the data to be aggregated, using fields not the subject of aggregate functions. The HAVING clause then further filters the data after the aggregation, using fields that are the subject of aggregate functions. The SELECT query below performs the same operation as that above, but data limited to those rows where the Price Code field is NZTRADE:

  SELECT CustomerCode, SUM(GrossAmount) as TotalGross

  FROM INHEAD

  WHERE PriceCode = 'NZTRADE'

  GROUP BY CustomerCode

  HAVING Sum(GrossAmount) > 100000

  ORDER BY CustomerCode

A HAVING clause filters data after the aggregation of a GROUP BY clause. For filtering based on line values prior to aggregation, use a WHERE clause.