How to Use Aggregate Functions in Backendless

When analyzing data, you may need to know the average salary of all employees, the quantity of goods in stock, the number of individual items in stock, the maximum or minimum cost, and so on. These tasks are easily handled with aggregate functions. Aggregate functions perform calculations using the values in a column in order to obtain a single resulting value.

Backendless supports several aggregate functions, such as:

• AVERAGE – to calculate the average
• COUNT – to calculate the number of rows in the query
• SUM – to calculate the sum of values
• MIN – to calculate the smallest value
• MAX – to calculate the largest value

Let’s take a closer look at how to work with aggregate functions using Backendless. In this article, we will look at using aggregate functions with Backendless REST API, but we also show how to work with aggregate functions using Backendless SDK for iOS API, Backendless SDK for Android/Java API, Backendless SDK for .NET API, and Backendless SDK for JavaScript.

You can find detailed information on these APIs in the developer documentation in the appropriate sections:

• Android: AVERAGE – COUNT – SUM – MIN – MAX
• iOS: AVERAGE – COUNT – SUM – MIN – MAX
• JS: AVERAGE – COUNT – SUM – MIN – MAX
• .NET: AVERAGE – COUNT – SUM – MIN – MAX
• REST: AVERAGE – COUNT – SUM – MIN – MAX

To begin, we will create a new application. Log in to Backendless Console (you can create a free account here if you don’t already have one) and press the CREATE APP button. We’re going to save the new application with the name Aggregate_Functions, but you can call it whatever you would like.

Next, go to the Data tab and create a new table called Products.

Add a few columns (Name, Manufacturer, Count, Price) to our table. To do this, go to the SCHEMA tab:

Set the field values:

• Name = Name: Name, Type: STRING;
• Manufacturer = Name:  Manufacturer, Type: STRING;  
• Count = Name: Count, Type: INT;
• Count = Name:  Price, Type: DOUBLE;

Now let’s add some records to the table:

You can see the full test data below:

AVERAGE
Now, let’s find the average value of the price of goods. To do this, we need two keys: Application ID and REST API key. These can be found on the home page of your application:

Use the terminal program cURL and execute the following query (replace my keys with your own):

curl -S 'https://api.backendless.com/31DCF5B5-EA8D-EA46-FF7D-ABFDF4922A00/188C735D-D5ED-DF07-FF3B-DB2CCEB36200/data/Products?props=Avg(Price)'

You should get the following answer in JSON format:

[
  {
    "avg":763.4399999999998,
    "___class":"Products"
  }
]

You can also use an alias for the resulting value (the value must be URL-encoded, so the spaces are replaced with %20 ):

curl -S 'https://api.backendless.com/31DCF5B5-EA8D-EA46-FF7D-ABFDF4922A00/188C735D-D5ED-DF07-FF3B-DB2CCEB36200/data/Products?props=Avg(Price)%20as%20averagePrice'

The answer will be as follows:

[
  {
    "averagePrice":763.4399999999998,
    "___class":"Products"
  }
]

At the sampling stage, you can apply filtering. For example, let’s find the average price for products of a particular manufacturer; for this, we add to our query & where=Manufacturer=’Nokia’ . We again need to replace some characters in the variable: the equals sign will be replaced with  %3D , and the single brackets will be %27 :

curl -S 'https://api.backendless.com/31DCF5B5-EA8D-EA46-FF7D-ABFDF4922A00/188C735D-D5ED-DF07-FF3B-DB2CCEB36200/data/Products?props=Avg(Price)%20as%20averagePriceForNokia&where=Manufacturer%3D%27Nokia%27'

This request will return the average price for all phones with the manufacturer of Nokia:

[
  {
    "averagePriceForApple":553.125,
    "___class":"Products"
  }
]

Now let’s try to find the average quantity of all products:

curl -S 'https://api.backendless.com/31DCF5B5-EA8D-EA46-FF7D-ABFDF4922A00/188C735D-D5ED-DF07-FF3B-DB2CCEB36200/data/Products?props=Avg(Price*Count)'

Here is the response we should get:

[
  {
    "avg":12221.1,
    "___class":"Products"
  }
]

COUNT
The count function counts the number of records in the table. Let’s try the following query:

curl -S 'https://api.backendless.com/31DCF5B5-EA8D-EA46-FF7D-ABFDF4922A00/188C735D-D5ED-DF07-FF3B-DB2CCEB36200/data/Products?props=Count(objectId)%20as%20countProducts'

The result returned is:

[
  {
    "countProducts":10,
    "___class":"Products"
  }
]

SUM
The sum function calculates the sum of the column values. For example, let’s calculate the total quantity of goods:

curl -S 'https://api.backendless.com/31DCF5B5-EA8D-EA46-FF7D-ABFDF4922A00/188C735D-D5ED-DF07-FF3B-DB2CCEB36200/data/Products?props=Sum(Count)%20as%20sumProducts'

The result:

[
  {
    "sumProducts":167,
    "objectId":"06A4DE38-E67A-48CB-FFF4-9801643A1800",
    "___class":"Products"
  }
]

MIN
Let’s the minimum price for the product using the min function:

curl -S 'https://api.backendless.com/31DCF5B5-EA8D-EA46-FF7D-ABFDF4922A00/188C735D-D5ED-DF07-FF3B-DB2CCEB36200/data/Products?props=Min(Price)'

We get the response:

[
  {
    "min":505.5,
    "___class":"Products"
  }
]

MAX
Now let’s find the maximum price for the product using the max function:

curl -S 'https://api.backendless.com/31DCF5B5-EA8D-EA46-FF7D-ABFDF4922A00/188C735D-D5ED-DF07-FF3B-DB2CCEB36200/data/Products?props=Max(Price)'

And the response returned is:

[
  {
    "max":980.6,
    "___class":"Products"
  }
]

You can also use grouping in a request. For example, let’s say we want to find the maximum value of the goods for each manufacturer:

curl -S 'https://api.backendless.com/31DCF5B5-EA8D-EA46-FF7D-ABFDF4922A00/188C735D-D5ED-DF07-FF3B-DB2CCEB36200/data/Products?props=Max(Price),Manufacturer&groupBy=Manufacturer'

In return, we get a list where we can see how much the most expensive product of each manufacturer costs:

[
  {
    "max":980.6,
    "Manufacturer":"Apple",
    "___class":"Products"
  },
  {
    "max":680.9,
    "Manufacturer":"Huawei",
    "___class":"Products"
  },
  {
    "max":600.75,
    "Manufacturer":"Nokia",
    "___class":"Products"
  },
  {
    "max":845.95,
    "Manufacturer":"Samsung",
    "___class":"Products"
  }
]

Let’s go ahead and sort the products in descending order from the highest price to the lowest, by manufacturer:

curl -S 'https://api.backendless.com/31DCF5B5-EA8D-EA46-FF7D-ABFDF4922A00/188C735D-D5ED-DF07-FF3B-DB2CCEB36200/data/Products?props=Max(Price)%20as%20maxPriceOnManufacturer,Manufacturer&groupBy=Manufacturer&sortBy=maxPriceOnManufacturer%20DESC'

We get a sorted list with the name of the manufacturer and the cost of its most expensive product in descending order:

[
  {
    "maxPriceOnManufacturer":980.6,
    "Manufacturer":"Apple",
    "___class":"Products"
  },
  {
    "maxPriceOnManufacturer":845.95,
    "Manufacturer":"Samsung",
    "___class":"Products"
  },
  {
    "maxPriceOnManufacturer":680.9,
    "Manufacturer":"Huawei",
    "___class":"Products"
  },
  {
    "maxPriceOnManufacturer":600.75,
    "Manufacturer":"Nokia",
    "___class":"Products"
  }
]

Enjoy using Backendless services!