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.
- COUNT
- SUM
- MAX
- MIN
- AVG
Example
In this example, suppose the following Salary table of the employees in a company.
Employee Id | Gross | Deduction | NetPay |
---|---|---|---|
1002 | 12,000 | 2,000 | 10,000 |
1004 | 20,000 | 1,000 | 18,000 |
1003 | 24,000 | 1,000 | 23,000 |
1005 | 30,000 | 00 | 30,000 |
1001 | 40,000 | 3,000 | 37,000 |
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: