The COUNT function in Teradata returns the total number of qualified rows in the value expression.
COUNT ( [TYPE] value_expression );
- 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.
Let’s take an example to understand the concept of the Teradata COUNT function.
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;
Teradata COUNT DISTINCT
select count(DISTINCT supplierID) as Total_Suppliers from products;
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;