Teradata count distinct

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

Teradata Count Syntax:

COUNT([TYPE] value_exoression)

Where

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.

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.

SELECT COUNT (DISTINCT DeptNo) 
FROM Employee ;

The system responds with the following report.

   Count(Distinct(DeptNo))
   -----------------------
                         5

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:

SELECT COUNT(*) FROM Employee;

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.

Example

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.

   Count(DeptNo) 
   --------------
              21