Teradata Aggregate FunctionsAggregate 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.
ExampleIn this example, suppose the following Salary table of the employees in a company.
1. COUNTCOUNT 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. MAXMAX 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. MINMIN 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. AVGAVG 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. SUMSUM 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 DataOperations 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 ClausesAn aggregate function can appear in the following types of clauses:
DISTINCT OptionThe 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: Next TopicTeradata CASE & COALESCE |