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