HAVING Clause

Filters rows that have been grouped by a grouping clause.

Syntax

HAVING expression

Components

expression

Any Boolean expression. Can include aggregate functions, as well as simple filters on columns.

Usage

The HAVING clause is semantically similar to the WHERE clause, but can be used only in a query that specifies a GROUP BY clause. The HAVING clause filters rows after they have been processed by the GROUP BY clause. Unlike the WHERE clause, the HAVING clause allows the use of aggregates in the expression. Its function is to eliminate some of the grouped result rows.

Example

The HAVING clause filters the rows that have been grouped by the GROUP FILTER, GROUP BY, and GROUP ORDER clauses:

CREATE WINDOW Window1 SCHEMA (Symbol STRING, MaxPrice INTEGER)
PRIMARY KEY DEDUCED
KEEP ALL
AS 
SELECT T.Symbol, max(T.Price) MaxPrice
FROM Trades T
GROUP FILTER rank() < 10
GROUP BY T.Symbol
GROUP ORDER BY T.Volume DESC
HAVING max(T.Price) > 100 AND T.Symbol ='IBM';