Teradata Count Distinct

The count function returns the total number of qualified rows in the value expression. Teradata count distinct returns the total number of distinct rows.

Teradata Count Distinct Syntax

COUNT ( [TYPE] value_expression );

Here,

• 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 – All values of value_express that are not null, including duplicates, are included in the computation.
• TYPE as DISTINCT – Exclude duplicates specified by value_expression from the expression. This is used to get the DISTINCT count in Teradata.
• TYPE as * – Counts all the rows in the group of rows on which count operates.

Teradata Count Distinct Example

Consider the following employee table.

You can use count(distinct) to find out the number of distinct departments in the employee table as below.

SELECT COUNT(distinct department)
FROM Teradatapoint.employee;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Count(Distinct(department))
---------------------------
4

Table Preparation

CREATE TABLE Teradatapoint.employee 
(
emp_id integer,
emp_name varchar(50),
emp_phone varchar(10),
emp_gender char(1),
department varchar(20)
)primary index(emp_id);

insert into teradatapoint.employee values (101,'Kalyan Roy','9620139678','M','HR');
insert into teradatapoint.employee values (102,'Rajesh Sharma','9611895588','M','ADMIN');
insert into teradatapoint.employee values (103,'Rupali Sharma','8884692570','F','SALES');
insert into teradatapoint.employee values (104,'Dipankar Sen','9957889640','M','HR');
insert into teradatapoint.employee values (105,'Sunitha Rai','9742067708','F','SALES');
insert into teradatapoint.employee values (106,'Parag Barman','8254066054','M','MAKETING');
insert into teradatapoint.employee values (107,'Vinitha Sharma','9435746645','F','ADMIN');
insert into teradatapoint.employee values (108,'Abhishek Saha','9850157207','M','SALES');
insert into teradatapoint.employee values (109,'Rushang Desai','9850157207','M','SALES');
insert into teradatapoint.employee values (110,'Arvin Kumar','8892340054','M','ADMIN');