Teradata having clause

In Teradata having clause is a conditional expression that must be satisfied for a group of rows to be included in the result data.

SYNTAX:
Having search_condition

Having is 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. 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.

In this kind of queries, you can use WHERE, GROUP BY and HAVING clauses together, where the order of execution is as follows, 1. WHERE, 2. GROUP BY and 3. HAVING

Example of Having Clause in Teradata:

SELECT COUNT(employee)
FROM department
WHERE dept_no = 100
HAVING COUNT(employee) > 10;

Example of having clause in Teradata with grouping departments:

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

Please note that while using Having clause in Teradata, and there is some columns in the SELECT then you must have to use those same columns in GROUP BY to get the aggregated values.