Teradata Aggregate Function



The Aggregate functions prepare the summarization of values from one or more tables. Detail level of information will be lost after performing the aggregate on some specific tables.

Teradata support most common Aggregate function as below.

Function Name Detail
COUNT Provides the total number of rows from tables.
SUM Produces sum of all the values for the specific column(s)
MIN Returns the minimum value of a specific column
MAX Returns the maximum value of a specific column
AVG Returns the average value of the specific column

Aggregate Function Example

Consider the below customer table.

cust_id income age years_with_bank nbr_children gender marital_status
1363355 13087 22 1 0 M 2
1363329 0 6 0 0 F 1
1362672 16319 50 0 1 F 2
1362629 0 6 0 0 M 1
1362611 24115 48 8 1 F 2

COUNT Function

COUNT will provide the total numbers of records present in the table.

COUNT Example

The following query will return number of records from the customer table.

sel count(*) from teradatapoint.customer;

When executed, it produces following result.

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

   Count(*)
-----------
          5

 

SUM Function

SUM provides the summation for a specific column.

SUM Function Example

Suppose we want get the sum of income for all the customer.

sel sum(income) from teradatapoint.customer;

When executed, it produces following result.

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

Sum(income)
-----------
      53521

 

MIN Function

MIN returns the minimum value from a specific column.

MIN Function Example

Suppose, we want to get the minimum age of customer. 

SELECT MIN(age) FROM teradatapoint.customer;
When executed, it produces following result.

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

Minimum(age)
------------
           6

 

MAX Function

MAX returns the maximum value from a specific column.

MAX Function Example

SELECT MAX(age) FROM teradatapoint.customer;

When executed, it produces following result.

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

Maximum(age)
------------
          50

AVERAGE/ AVG Function

AVG returns the average value from a specific column.

AVERAGE Function Example

Suppose, we want to get the average age of customer.

SELECT AVG(age) FROM teradatapoint.customer;

When executed, it produces following result.

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

Average(age)
------------
          26