Admin GuidesAiimi

Aggregating Results

You can request data aggregations through your OData API query. This groups data and performs functions to summarise specific fields. The aggregated data can then be used for statical analysis. or as a summary.

Aggregations are performed using the query parameter $apply. This parameter can be added as part of the query parameter.

apiUrl/odata/entitySetName?username=username&api-key=key&$apply= ...

Aggregate a Single Property

$count - Returns the total number of results for a query. 

$apply=aggregate($count as TotalResults)

Non Array Type Aggregation Functions

Max - Returns the highest value from your results. Numeric fields only.

$apply=aggregate(Rating with max as MaxRating)

Min - Returns the lowest value from your results Numeric fields only.

$apply=aggregate(Rating with min as MinRating)

Sum - Adds together all the values of a field. Numeric fields only.

$apply=aggregate(OrdersValue with sum as TotalOrdersValue)

Average - Returns the mean of all the values. Numeric fields only.

$apply=aggregate(OrderValue with average as AverageOrderValue)

countdistinct - Returns the total number of unique results for a field. $apply=aggregate(OrderNumber with countdistinct as TotalUniqueOrders)

Combine aggregations - Combine aggregations in 1 query using commas to separate them. $apply=aggregate(Rating with max as MaxAge, PersonAge with average as AverageAge)

Group By Aggregations

GroupBy - Group the results of a field and return the unique values as buckets. When grouping you must use double brackets.

$apply=groupby((OrderNumber))

It is possible to group by multiple properties and this will return buckets for each distinct combination of values. These are separated by commas:

Combined GroupBy Group the results of 2 fields and return the unique values as buckets. Each field must be separated by commas. When grouping you must use double brackets.

 $apply=groupby((OrderNumber,Customer))

Value aggregations on Grouped Buckets.

You can combine groupby and aggregate functions to perform aggregations on each bucket. 

$apply=groupby((CustomerNumber), aggregate(OrderValue with average as AverageOrderValue))

This will return a bucket per unique value in CustomerNumber and the average order value for each as AverageOrderValue.

Last updated