16.20 - HAVING - Teradata Database - Teradata Vantage NewSQL Engine

An introduction to a conditional grouping clause in the SELECT request.

condition

One or more conditional Boolean expressions that must be satisfied by the results groups.

You can use aggregate operators in a HAVING condition.

You cannot specify BLOB, CLOB, UDT, or Period columns in the HAVING condition.

You cannot specify a SAMPLE clause within a subquery predicate within a HAVING clause.

HAVING condition filters 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.

Example: Creating a View with Aggregates

The following request illustrates the use of aggregates in a view definition. The result rows are grouped by department number and include only those rows with an average salary of $35,000 or higher.

    CREATE VIEW dept_sal (deptno, minsal, maxsal, avgsal) AS 
     SELECT deptno, MIN(salary), MAX(salary), AVG(salary) 
     FROM employee 
     GROUP BY deptno 
     HAVING AVG(salary) >= 35000;

Now perform the following SELECT request using this view:

    SELECT * 
    FROM dept_sal; 

The query returns the following response set:

    DeptNo         MinSal         MaxSal         AvgSal 
    ------     ----------      ---------     ----------
       600      28,600.00      45,000.00      36,650.00
       300      23,000.00      65,000.00      47,666.67
       700      30,000.00      45,000.00      37,666.67
       500      22,000.00      56,000.00      38,285.71

The following SQL request returns the response set that follows:

    SELECT deptno, minsal, minsal+10000, avgsal 
    FROM dept_sal 
    WHERE avgsal > (minsal + 10000);
    
    DeptNo         MinSal      (MinSal+10000)         AvgSal 
    ------     ----------   -----------------     ----------
       500      22,000.00            32000.00      38,285.71
       300      23,000.00            33000.00      47,666.67