The aggregate functions are used to summarize column data values stored in rows. Aggregates eliminate the detail information from the rows and only return the answer. Therefore, the result is one or more aggregated values as a single line or one line per unique value, as a group. The other characteristic of these functions is that they all ignore null values stored in column data passed to them.

Math Aggregates

The math aggregates are the original functions used to provide simple types of arithmetic operations for the data values. Their names are descriptive of the operation performed. The functions are listed below with examples following their descriptions. The newer, V2R4 statistical aggregates are covered later in this.

The SUM Function

Accumulates the values for the named column and prints one total from theaddition.

The AVG Function

Accumulates the values for the named column and counts the number of values added for the final division to obtain the average.

The MIN Function

Compares all the values in the named column and returns the smallest value.

The MAX Function

Compares all the values in the named column and returns the largest value.

The COUNT Function

Adds one to the counter each time a value other than null is encountered.

The aggregates can all be used together in a single request on the same column, or individually on different columns, depending on your needs.

The following syntax shows all six aggregate functions in a single SELECT to produce a single line answer set:

The following table is used to demonstrate the aggregate functions:

The next SELECT uses the Student table, to show all aggregates in one statement working on the same column:

Notice that Stanley's row is not included in the functions due to the null in his grade point average. Also notice that no individual grade point data is displayed because the aggregates eliminate this level of column and row detail and only returns the summarized result for all included rows. The way to eliminate rows from being included in the aggregation is through the use of a WHERE clause.

Since the name of the selected column appears as the heading for the column, aggregate names make for funny looking headings. To make the output look better, it is a good idea to use an alias to dress up the name used in the output. Additionally, the alias can be used elsewhere in the SQL as the column name.

The next SELECT demonstrates the use of alias names for the aggregates:

1 Row Returned

Notice that when using aliases in the above SELECT they appear as the heading for each column. Also the words Total, Average and Count are in double quotes. As mentioned earlier, the double quoting technique is used to tell the PE that this is a column name, opposed to being the reserved word. Whereas, the single quotes are used to identify a literal data value.

Aggregates and Derived Data

The various aggregates can work on any column. However, most of the aggregates only work with numeric data. The COUNT function might be the primary one used on either character or numeric data. The aggregates can also be used with derived data.

The following table is used to demonstrate derived data and aggregation:

This SELECT totals the salaries for all employees and show what the total salaries will be if everyone is given a 5% or a 10% raise:

1 Row Returned

Notice that since both TITLE and FORMAT require parentheses, they can share the same set. Also, the AVG function and dividing the SUM by the COUNT provide the same answer.