Using the Relational Data Framework › Use Expressions and Functions › Numeric Aggregate Functions › Return Values Based on Numeric Calculations

Return Values Based on Numeric Calculations

There may be instances when you want to retrieve a value that is based on a mathematical calculation of a collection of values. Aggregate functions perform simple numeric calculations, mostly on values in a specified column in a table. SQL NULL column values are ignored for all aggregate functions except the row count function, COUNT(*).

For example, the following statement returns the average of the values in the COST column from the WORKSTATIONS table:

OPSQL SELECT AVG (COST) FROM WORKSTATIONS

Note: This particular function is to be used on numeric data type columns only.

When using the INTO keyword on a SELECT statement, the stem variables created use the specified variable stem name, as in the following example using the WORKSTATIONS table:

ADDRESS SQL
“SELECT COUNT (*) INTO VAR FROM WORKSTATIONS”

The above statement results in the following stem variables being created:

VAR.0=1 (the number of values returned)
VAR.1=4 (the number of rows in the table)

When the INTO keyword is not used on a SELECT statement, as in the following example:

ADDRESS SQL
“SELECT MIN (COST) FROM WORKSTATIONS”

The variables created are:

MIN_COST.0=1 (the number of values returned)
MIN_COST.1=1200 (the lowest value in the COST column)

Note that the compound variable stem names above are created by prefixing the aggregate function name (MIN), followed by an underscore (_), followed by the column name (COST).