You are here

Teradata count distinct

Submitted by Asif Nowaj, Last Modified on 2019-12-18

Count returns a value that is the total number of qualified rows in value_expression.

Teradata Count Syntax:

COUNT([TYPE] value_exoression)


Value_expression => a literal or column expression for which the total count is computed. The expression cannot contain any ordered analytical or aggregate functions.

[TYPE] as ALL => that all non-null values of value_expression, including duplicates, are included in the total count. This is the default.

[TYPE] as DISTINCT => that a value_expression that evaluates to NULL or to a duplicate value does not contribute to the total count. This is used to get distinct count in Teradata.

[TYPE] as * => to count all rows in the group of rows on which COUNT operates.

COUNT is valid for any data type.

Teradata distinct count

Example of Teradata count distinct:

To determine the number of distinct departments in the Employee table, use COUNT (DISTINCT) as illustrated in the following SELECT COUNT.

FROM Employee ;

The system responds with the following report.


Example of Teradata count:

COUNT(*) reports the number of employees in each department because the GROUP BY clause groups results by department number.

   SELECT DeptNo, COUNT(*) FROM Employee 
   GROUP BY DeptNo 
   ORDER BY DeptNo;

Without the GROUP BY clause, only the total number of employees represented in the Employee table is reported:


Note that without the GROUP BY clause, the select list cannot include the DeptNo column because it returns any number of values and COUNT(*) returns only one value.


SELECT COUNT(deptno) FROM employee; 

The result of this SELECT is that COUNT returns a total of the non-null occurrences of department number.

Because aggregate functions ignore nulls, the two new employees are not reflected in the figure.


Discussion or Comment

If you have anything in mind to share, please bring it in the discussion forum here.