Teradata HAVING Clause

Teradata HAVING clause is used along with the GROUP BY clause to apply the filter on the grouped data.

In our previous tutorial, we have seen how to group data using the GROUP BY clause. However, we don’t want to select all the records which are grouped by the GROUP BY clause. We would like to filter the result set based on some criteria. Here, group by clause can be used to perform the same.

Teradata HAVING syntax

The syntax for using the HAVING clause is as below.

HAVING search_condition

Here,

• 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.

Teradata HAVING example

The following SQL statement list the number of employee in each department and only include those departments with more than 1 employee.

SELECT COUNT(EMPNO),DEPTNO
FROM TUTORIALSBOOK.Employee
GROUP BY DEPTNO
HAVING COUNT(EMPNO) >=2;

Output:

SELECT COUNT(EMPNO),DEPTNO
FROM TUTORIALSBOOK.Employee
GROUP BY DEPTNO
HAVING COUNT(EMPNO) >=2;

 *** Query completed. 2 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

Count(EMPNO)       DEPTNO
------------  -----------
           2           30
           4           10

As you can see from the above result that DEPTNO 20 and 40 omitted from the result due to the condition in the having clause.