Teradata Aggregate Functions

Aggregate functions are typically used in arithmetic expressions. Aggregate functions operate on a group of rows and return a single numeric value to each group's result table.

Teradata supports common aggregate functions. They can be used with the SELECT statement.

1. COUNT
2. SUM
3. MAX
4. MIN
5. AVG

Example

In this example, suppose the following Salary table of the employees in a company.

1. COUNT

COUNT aggregate function is used to count the number of records of the table.

The following example counts the number of rows in the above Salary table.

Output

5 

2. MAX

MAX aggregate function returns the largest value of the specified column in the table.

The following example returns the maximum employee net salary.

Output

37,000 

3. MIN

MIN aggregate function returns the smallest value of the specified column.

The following example returns the minimum employee net salary from the Salary table.

Output

10,000

4. AVG

AVG Aggregate function returns the average value of the specified column.

The following example returns the average employee's net salary from the table.

Output

23,600

5. SUM

SUM aggregate function sums up the values of the specified column.

The following example calculates the sum of the employee's net salary from the Salary table.

Output

118,000

Aggregate Operations on Floating Point Data

Operations involving floating-point numbers are not always associative due to approximation and rounding errors. For example, ((A + B) + C) is not always equal to (A + (B + C)).

The non-associatively of floating-point arithmetic can also affect aggregate operations. Every time we can get different results while using an aggregate function on a given set of floating-point data.

When Teradata Database performs an aggregation, it accumulates individual terms from each AMP involved in the computation and evaluates the terms in order of arrival to produce the final result.

The order of evaluation can produce slightly different results, and the order in which individual AMPs finish their part of the work is unpredictable, the results of an aggregate function on the same data on the same system can vary.

Aggregates Clauses

An aggregate function can appear in the following types of clauses:

• WHERE: The WHERE clause of an ABORT statement to specify an abort condition. But an aggregate function cannotappear in the WHERE clause of a SELECT statement.
• HAVING: A HAVING clause to specify a group condition.

DISTINCT Option

The DISTINCT option specifies that duplicate values which are not to be used when an expression is processed.

The following SELECT returns the number of unique job titles in a table.

Output

2000
1000
00
3000

A query can have multiple aggregate functions that use DISTINCT with the same expression, such as:

A query can also have multiple aggregate functions that use DISTINCT with different expressions, such as: