Aggregations

Overview

An Aggregation is a dynamic data provider that represents a database Column that is aggregated over a set of records, therefore it belongs to a Table.

Just like real database columns, calculations may be placed as fields on forms, used as data providers for different components, and requested programmatically.

Get Started

To work with Aggregations you need to open the Table where you will add them using the Table Editor.

Add an Aggregation

Add Aggregation

1. In the Table Editor, select the Aggregations tab at the bottom after all the Columns

2. Select the solution where you want to add the Aggregation and click the Add button

3. Set the property with a valid name

4. Select the Type of the Aggregation, the available options are

   1. Count, the number of records in an entire Foundset containing a non-null value for a column

   2. Count distinct, same as Count but count unique values

   3. Maximum, the largest value for a numeric column in an entire foundset

   4. Minimum, the smallest value for a numeric column in an entire Foundset

   5. Average, the average value for a numeric column in an entire Foundset

   6. Sum, the sum of all the values for a numeric column in an entire Foundset

5. Select the Column that will be aggregated

6. Save the editor

An aggregation is declared at the Solution level and is available throughout the solution in which it is declared. If it's declared in a Module, it will be available in all solutions that include the module

Edit an Aggregation

Similar to adding an Aggregation, open the Aggregations tab in the Table Editor, select the one you need to edit, and change any of the three properties: Name, Type, or Column then Save the editor

Delete an Aggregation

Similar to editing an Aggregation, open the Table Editor, select the one you need to delete, and click on the button "Remove" at the bottom of the list (you will be prompted to confirm)

Unresolved Data Bindings When you edit the name of an Aggregation or delete it, you may also create errors in your application if you have objects bound to it, such as a field on a form. These errors can be resolved in Servoy Developer.

Remarks

The scope of an Aggregation is the entire Foundset using a SQL query which means that the result will consider only the records represented by the WHERE clause of the Foundset.

For example, an Aggregation could be applied to a related Foundset, products_to_order_details.sum_quantity, to return the total number of product units ordered.

Because Aggregations are derived from SQL queries, they may not reflect data changes in the client 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 Grid, it may result in a query for each record displayed and performance may degrade.