HAVING clause

Syntax

HAVING boolean

Usage

The HAVING clause is syntactically nearly identical to the selection condition version of the WHERE clause. Unlike WHERE, however, the HAVING clause filters rows after they have been processed by the GROUP BY clause (if one is present) and can itself contain aggregate functions. The selection condition in this clause can also include literals, column references from the query's data sources listed in the FROM clause, operators, scalar and miscellaneous functions, and parentheses. The HAVING clause cannot include subqueries or references to streams that are specified as non-events in the MATCHING clause. Column references within the selection condition must refer to columns in one of the query's data sources.

The HAVING clause is generally used in conjunction with the GROUP BY clause (however, a GROUP BY clause can be used without a HAVING clause) or to create selection conditions that require the use of aggregate functions. The HAVING clause can also be used in conjunction with the WHERE clause, where the WHERE clause filters rows before aggregators are executed on them, and the HAVING clause then filters the results.

Restrictions

• A HAVING clause cannot include subqueries.

• A HAVING clause cannot include references to non-events (data streams specified with !event-name syntax in the MATCHING clause).

See Also

• Database Statement

• Query Statement

• Remote Procedure Statement

• GROUP BY

• WHERE

Example

In the following example, the HAVING clause filters out groups where the average value of the Salary column is less than 3000:

INSERT INTO OutStream
SELECT I.Dept, AVG(I.Salary)
FROM InStream AS I KEEP ALL
GROUP BY I.Dept
HAVING AVG(I.Salary) >= 3000.00;