The
HAVING clause is used to filter output records.
The syntax of the
HAVING clause is as follows:
HAVING <BooleanExpression>
You can use the
HAVING clause with any Boolean expression, such as:
- Numeric and string value
comparison:
{= , <>, <, <=, >, >=}
- Null value evaluation:
<attribute> IS {NULL, NOT NULL}
- Set operations: such as
SUBSET and
IS_MEMBER_OF
- Grouping keys of the source
statement:
<attribute-list> IN <source-statement>
In the following example, the results include only sales representatives
who generated at least $10,000:
Return Reps AS
SELECT SUM(Amount) AS SalesTotal
GROUP BY SalesRep
HAVING SalesTotal > 10000
Note that
HAVING clauses may refer only to attributes defined in
the same statement (such as aliased attributes defined by a
SELECT clause), as shown in these examples:
// Invalid because Price is not defined in the statement (i.e., Price is a corpus attribute).
Return results AS
SELECT SUM(Price) AS TotalPrices
GROUP BY WineType
HAVING Price > 100
// Valid because TotalPrices is defined in the statement.
Return results AS
SELECT SUM(Price) AS TotalPrices
GROUP BY WineType
HAVING TotalPrices > 100