Count returns a value that is the total number of qualified rows in value_expression.
Teradata Count Syntax:
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.
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