Teradata COUNT Function

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

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.

Example

Let’s take an example to understand the concept of the Teradata COUNT function.

Table Preparation

CREATE VOLATILE TABLE Products
(
ProductID INT NOT NULL,
ProductName varchar(100) NOT NULL,
SupplierID INT NOT NULL,
CategoryID INT NOT NULL,
Unit INT NOT NULL,
Price INT
)
unique primary index(ProductID)
on commit preserve rows;

insert into products (1,'Batteries',1,1,10,25);
insert into products (2,'Wooden single drawer',1,2,20,47);
insert into products (3,'Classic wooden chair',2,2,15,31);
insert into products (4,'Magazine chair',1,1,10,NULL);
insert into products (5,'Chair with love',2,1,20,31);
insert into products (6,'Round chair',2,2,15,11);

Now you can check the data of the table using the select statement.

select * from Products order by 1;

Teradata COUNT ALL

select count(ALL supplierID) as Total_Suppliers from products;

This is the same as,

select count(supplierID) as Total_Suppliers from products;

Output

Teradata COUNT DISTINCT

select count(DISTINCT supplierID) as Total_Suppliers from products;

Output

As you can see that only the unique values are counted.

Teradata COUNT with Group BY

select CategoryID, count(DISTINCT supplierID) as Total_Suppliers from products 
group by CategoryID;

Output