Aggregate Functions

An aggregate function performs an operation on sets of values and returns a single result.

Syntax

An aggregate function can be any one of the following:

AVG'(' value_returned_expression ')': Calculates the mean of the values.
Input: A numeric expression

Example:
```
AVG(SALES.AMOUNT_SOLD)
```
COUNT'(*)' : Counts all the rows, including those with null values.
Input: None.

Example:
```
COUNT(*)
```
COUNT'(' value_returned_expression_list ')': Counts the rows where the input expression is non-null.
Input: A numeric or other type of expression.

Example:
```
COUNT(SALES.CUST_ID)
```
COUNT'(' DISTINCT value_returned_expression_list ')': Counts the unique, non-null values in the input expression.
Input: A numeric or categorical expression.

Example:
```
COUNT(DISTINCT SALES.CUST_ID)
```
MAX'(' value_returned_expression ')': Returns the maximum value in the input.
Input: A numeric or comparable expression.

Example:
```
MAX(SALES.AMOUNT_SOLD)
```
MIN'(' value_returned_expression ')': Returns the minimum value in the input.
Input: A numeric or comparable expression.

Example:
```
MIN(SALES.AMOUNT_SOLD)
```
SUM'(' value_returned_expression ')': Calculates the total sum of the values in the input.
Input: A numeric expression.

Example:
```
SUM(SALES.AMOUNT_SOLD)
```
FIRST'(' value_returned_expression [, nulls_option] ')': Returns the first value in a group. Optionally ignores null values.
Input: A numeric or comparable expression, and an optional null handling option.

Example:
```
FIRST(SALES.AMOUNT_SOLD,IGNORE NULLS)
```
LAST'(' value_returned_expression [, nulls_option] ')': Returns the last value in a group. Optionally ignores null values.
Input: A numeric or comparable expression, and an optional null handling option.
Example:
```
LAST(SALES.AMOUNT_SOLD)
nulls_option ::= [IGNORE | RESPECT] NULLS
```