HAVING Clause - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Purpose

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

Syntax



Syntax Elements

HAVING
An introduction to 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, scalar subqueries, and DEFAULT functions as conditional expressions with HAVING.
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.

Usage Notes

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

You can use 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.

Related Topics

For more information related to the HAVING clause, see: