Aggregation is an operation that combines values from multiple records into a single value.
Examples of aggregation include taking an average of multiple numerical values or joining multiple text values into a single text string.
Special Aggregate Functions can be used within Model Calculation expressions to perform such operations.
Within Model Calculation expressions, Aggregate Functions can currently only be used on child records. It is also possible to perform aggregation on “grandchild” and other Groups lower in the database hierarchy.
Aggregation in Model Calculations is performed per-Location (in other words, it’s not possible to perform aggregation across all Locations within a Project).
Aggregation is performed slightly differently within Template Studio expressions. Please see the Aggregation in Templates guide for more information.
For general information on OpenGround expressions, please see our Expressions Guide.
Table of Contents
Aggregation Example
The concept of Aggregation is easiest to demonstrate with an example.
For this example, we will calculate the maximum SPT N Value for each Location.
The SPT Group is a child of LocationDetails, so we’ll need to perform this calculation in the LocationDetails Group.
To do this, we’ll add a custom calculated Header to the LocationDetails Group that uses the MaxAll() function in this expression:
MaxAll([SPT.NValue])Looking at the results, we see that the maximum N Value has been calculated for each of the BH Locations that contain SPT data. No value is returned for CPT1 and TP1 where SPT’s were not performed.
Aggregating “Grandchild” Records
As noted above, it is possible to perform aggregation on “grandchild” and other Groups lower in the database hierarchy.
In this example, we will find the maximum Moisture Content for each Location.
The MoistureContent Group is a child of SampleInformation, which is a child of LocationDetails, making MoistureContent a “grandchild” of LocationDetails.
To do this, we’ll add a custom calculated Header to the LocationDetails Group that uses the MaxAll() function in this expression:
MaxAll([MoistureContent.MoistureContent])Looking at the results, we see that the maximum Moisture Content has been calculated for each of the BH Locations that contain Moisture Content data. No value is returned for CPT1 and TP1 where Moisture Content tests were not performed.
Key Field Ordering
For some Aggregate Functions, such as first() and last(), the order of the child records is important.
In these scenarios, the child records are ordered in ascending order by the Key Headers.
More specifically, the Key Headers are ordered based on the ‘Display Order’ defined in System>Configuration Packs>Manage Model. Many Groups have multiple Key Headers that determine this. For example, the SampleInformation Group would be ordered by LocationID (parent Key Header), then DepthTop (Display Order = 2), then by SampleReference (Display Order = 3), and so on for the rest of the Key Headers.
To demonstrate this, we will calculate the first Moisture Content for each Location.
To do this, we’ll add a custom calculated Header to the LocationDetails Group that uses the first() function in this expression:
first([MoistureContent.MoistureContent])Looking at the results, we see that the first MoistureContent has been calculated for each of the BH Locations that contain Moisture Content data. No value is returned for CPT1 and TP1 where Moisture Content tests were not performed.
Note that for BH2 at 2.75 m, there are 2 records with the same parent Keys (the columns in blue from the LocationDetails and SampleInformation Groups), so the SpecimenReference is ultimately used to determine the ordering.