Aggregate Function

Aggregate function is which performs a calculation or computation on certain set of values rather than on a single value. In computer science all the management and spreadsheet systems support a set of functions that can operate on a set of selected records or cells. Usually aggregate functions include things such as average, count, maximum, median, minimum, mode and sum. These functions are common in database management systems and spreadsheets.

In addition aggregate function returns the requested data by retrieving stored values and calculating the remaining values. For instance for ‘return value’ the same data type as the aggregated variable will be shown.  In syntax things such as aggregate, var, using, aggmap, from, fromspec, fromvar and textvar are used.

The keywords and their functions

If the word var is typed than the name of the variable whose data will be calculated and returned. This is often numeric though things like Boolean, Text, Datetime, or Date depending on the operator specified in the relation statements in the aggmap specification. And if the word using is typed, this denotes that the aggregation is done using the specified aggmap. If we ignore this phrase, the function uses the default aggmap for the variable as previously specified using the aggmap command or the aggmap property.  In aggmap the name of a previously-defined aggmap that specifies how the data will be aggregated.

The keyword FROM denotes that the detail data is obtained from a different project. This is the only way to specify the variable from which detail data should be received when doing aggregation. ‘fromspec’ is an arbitrarily dimensioned variable, formula, or relation from which the detail data for the aggregation is obtained.

The keyword FROMVAR denotes that the detail data is received from various objects to perform a capstone aggregation. And FORCECALC specifies that any value that is not specified in the aggmap’s PRECOMTUE clause should also be recalculated even in the case storing a value in a desired cell. The usage of the FORCECALC keyword helps if people want to change the data cells and see the changed values reflected in dynamically- computed aggregate cells.

The proper usage

All the aggregate functions can be used in select list in the ‘having’ clause of a select statement that includes a 'group by' clause. This cannot be used in a ‘where’ clause or in a ‘join’ condition. In addition, a select statement with aggregate functions in its select list often includes a WHERE clause that restricts the rows to which the aggregate is applied.

Questions:

• What is aggregate function?
• What is the importance of aggregate function?