Aggregation

Reference documentation for an Aggregation

Overview

An aggregation is a data provider which represents a database column that is aggregated over a set of records.

At runtime, aggregations are computed in the context of a foundset. The value is derived from a SQL query, which takes the form of a SQL Aggregate Function and appends the WHERE clause used by the foundset's query.

Properties Summary

At design-time aggregations have the following properties:

Property	Summary
Name	The name of the aggregation
Type	The aggregating function to be applied to the column
Column	The column containing values that are aggregated

Properties Details

Name

The name by which the aggregation will be available as a data provider throughout the solution in which it is declared, as well as any modules containing it.

Type

There are five types of aggregations:

• count - The number of records in an entire foundset containing a non-null value for a column

• count(distinct) - The number of distinct records in an entire foundset containing a non-null value for a column

• maximum - The largest value for a numeric column in an entire foundset

• minimum - The smallest value for a numeric column in an entire foundset

• average - The average value for a numeric column in an entire foundset

• sum - The sum of all the values for numeric column in an entire foundset

Column

The column containing values that are aggregated.

Example

Assume an aggregation, record_count, declared on the customer table. The aggregation type is count and the column is customerid. The aggregation is available for any foundset based on the customers table.

function printRecordCcount(event) {
    application.output(record_count);       // print record count before find
    if(foundset.find()){                // Find all customers where city starts with 'B'
        city = 'B%';
        foundset.search();
        application.output(record_count);   // print the record count after find
    }
}

After the find, the aggregation is re-queried using the foundset's new WHERE clause.

SELECT COUNT(customerid) AS record_count FROM customers WHERE city LIKE ? LIMIT ?

Performance

Because aggregations are derived from SQL queries, they may not reflect data changes, seen in the client, but not yet committed to the database. Aggregations will refresh after outstanding changes are committed.

SQL Aggregate Functions may be expensive operations, depending on the size and structure of a database table and the nature of the aggregation. Developers are encouraged to use discretion when working with aggregations. For example, when an aggregation is shown in a table-view form, it may result in a query for each record in displayed on the form, and performance may degrade.