Performing a calculation on a group of database objects may be a complex task. For example, to calculate the sum of all orders for a customer would require retrieving all that customer’s orders then iterating over them to calculate the mathematical sum of all amounts. This was yesterday! As of Backendless 4.4.0, you can use aggregate functions to calculate the average, sum, maximum and minimum values for a collection of objects without retrieving them from the server. Additionally, the system supports calculating object count for all records in the database or a record subset.
To use an aggregate function, simply request a property in a data retrieval request in the following format (the example below is for calculating the sum for the orderAmount column):
GET https://api.backendless.com/<app id>/<REST API key>/data/Orders?props=Sum(orderAmount)
The returned object includes the sum property with the calculated value:
[
{
"sum": 14507795640,
"___class": "Orders"
}
]
The name of the property can be modified by assigning an alias (using %20 to replace the spaces in the URL):
GET https://api.backendless.com/<app id>/<REST API key>/data/Orders?props=Sum(orderAmount)%20as%20totalAmount
The result contains a value for the property named after the alias:
[
{
"totalAmount": 14507795640,
"___class": "Orders"
}
]
Results can be grouped by a column. The column could be either in the same table or a related one. For example, the following request retrieves the sum of all orders grouped by related country:
GET https://api.backendless.com/<app id>/<REST API key>/data/Orders? props=Sum(orderAmount)&groupBy=relatedCountry.countryName
Unlike the response above, the result for this query includes a collection of objects, each containing the sum for a related country:
[
{
"sum": 1324231,
"countryName": "Italy",
"___class": "Orders"
},
{
"sum": 5675675,
"countryName": "France",
"___class": "Orders"
},
{
"sum": 2342234,
"countryName": "Ireland",
"___class": "Orders"
}
]
It is also possible to apply a filter on the grouped values. This can be done using the having clause. For example, the request below retrieves only the groups of countries where the total order amount is greater than 10000:
GET https://api.backendless.com/<app id>/<REST API key>/data/Orders? props=Sum(orderAmount)& groupBy=relatedCountry.countryName& having=Sum(orderAmount)>10000
For more information about aggregate functions see the Backendless API documentation: