HAVING clauses

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