HAVING Clause

Purpose

Specifies a conditional expression that must be satisfied for a group of rows to be included in the result data.

Syntax

where:

Syntax Element …

Specifies …

HAVING

the conditional clause in the SELECT statement.

search_condition

one or more conditional expressions that must be satisfied by the result rows. You can specify aggregate operators and DEFAULT functions as HAVING search conditions.

HAVING search_condition selects rows from a single group defined in the SELECT expression list that has only aggregate results, or it selects rows from the group or groups defined in a GROUP BY clause.

The HAVING search condition cannot reference BLOB, CLOB, ARRAY, or VARRAY columns.

If you specify the value for a row‑level security constraint in a search condition, that value must be expressed in its encoded form.

ANSI Compliance

The HAVING clause is ANSI SQL:2011-compliant.

HAVING Clause

The rules and restrictions are:

You cannot specify LOB columns in the HAVING search condition.

The conditional expression can define one or more aggregates (for example, MAX, MIN, AVG, SUM, COUNT) and can be applied to the rows of the following group conditions:

A single group defined in the SELECT expression list, which has only aggregate results

One or more groups defined in a GROUP BY clause

HAVING cannot be specified in a recursive statement of a recursive query; however, a nonrecursive seed statement in a recursive query can specify the HAVING clause.

If you specify the value for a row‑level security constraint in a search condition, that value must be expressed in its encoded form.

You can specify a scalar subquery as an operand of a scalar predicate in the HAVING clause of a query.

The following rules apply to the use of the DEFAULT function as part of the search condition within a HAVING clause:

You can specify a DEFAULT function with a column name argument within a predicate. The system evaluates the DEFAULT function to the default value of the column specified as its argument. Once the system has evaluated the DEFAULT function, it treats it like a constant in the predicate.

You can specify a DEFAULT function without a column name argument within a predicate only if there is one column specification and one DEFAULT function as the terms on each side of the comparison operator within the expression.

Following existing comparison rules, a condition with a DEFAULT function used with comparison operators other than IS NULL or IS NOT NULL is unknown if the DEFAULT function evaluates to null.

A condition other than IS NULL or IS NOT NULL with a DEFAULT function compared with a null evaluates to unknown.

IF a DEFAULT function is used with …	THEN the comparison is …
IS NULL	TRUE if the default is null Else it is FALSE
IS NOT NULL	FALSE if the default is null Else it is TRUE

See SQL Functions, Operators, Expressions, and Predicates for more information about the DEFAULT function.

You cannot specify a SAMPLE clause in a subquery used as a HAVING clause predicate.

Evaluation Order of WHERE, GROUP BY, and HAVING Clauses

When the WHERE, GROUP BY, and HAVING clauses are used together in a SELECT statement, the order of evaluation is as follows:

1 WHERE

2 GROUP BY

3 HAVING

HAVING Rules

Tables referenced in a HAVING clause must be specified in one of the following:

FROM clause

WHERE clause

SELECT expression list

Non-aggregate condition

Aggregating a Join In a HAVING Clause

Teradata SQL supports the HAVING clause when referencing columns from two or more tables.

Example : Simple HAVING Clause Examples

The following example shows the use of HAVING as applied to the aggregate results of a single group defined in the SELECT list, which is particularly useful in a SELECT subquery.

     SELECT COUNT(employee)

     FROM department

     WHERE dept_no = 100

     HAVING COUNT(employee) > 10;

The following SELECT statements are additional examples of the correct use of the HAVING clause.

     SELECT SUM(t.a)

     FROM t,u

     HAVING SUM(t.a)=SUM(u.a);

     SELECT SUM(t.a), SUM(u.a)

     FROM t,u

     HAVING SUM(t.b)=SUM(u.b);

     SELECT SUM(t.a)

     FROM t,u

     HAVING SUM(t.b)=SUM(u.b)

     AND    u.b = 1

     GROUP BY u.b;

Example : Grouping Departments by Average Salaries

Display salary ranges for specified departments whose salaries average more than $37,000:

     SELECT dept_no, MIN(salary), MAX(salary), AVG(salary)

     FROM employee

     WHERE dept_no IN (100,300,500,600)

     GROUP BY dept_no

     HAVING AVG(salary) > 37000;

The result is:

     dept_no  MIN(salary)     MAX(salary)       AVG(salary)

     ‑‑‑-‑‑‑  ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑   ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑

        300        23,000.00       65,000.00         47,666.67

        500        22,000.00       56,000.00         38,285.71

Example : Using a HAVING Clause to Aggregate a Join

The columns named price and sales_qty are from two different tables table_1, unit_price_cost, and table_2, sales_hist. Use the following SELECT statement to find which category of items is sold for a profit margin greater than $1000.

     SELECT table_1.category,

     (table_2.price - table_2.cost) * SUM (table_1.sales_qty) AS margin

     FROM sales_hist AS table_1, unit_price_cost AS table_2

     WHERE table_1.prod_no=table_2.prodno

     GROUP BY table_1.category, table_2.price, table_2.cost

     HAVING margin > 1000;

A subquery can have a join on a view with an aggregate operation and a HAVING clause that references more than one table.

For More Information

For more information related to the HAVING clause, see:

“WHERE Clause” on page 119

“QUALIFY Clause” on page 168