Message:

Subscribe rss
Blog categories
All | Backendless features
How to Use Aggregate Functions in Backendless
April 2, 2019 by Vladimir Yalovy

How to Use Aggregate Functions

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 BackendlessIn 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:

To begin, we will create a new application. Log in to your account on https://develop.backendless.com/ (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.

How to Use Aggregate Functions 01

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

How to Use Aggregate Functions 02

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

How to Use Aggregate Functions 03

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:

How to Use Aggregate Functions 04

You can see the full test data below:

How to Use Aggregate Functions 05

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:

How to Use Aggregate Functions 06

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

You should get the following answer in json format:

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

The answer will be as follows:

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 :

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

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

Here is the response we should get:

COUNT

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

The result returned is:

SUM

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

The result:

MIN

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

We get the response:

MAX

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

And the response returned is:

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:

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

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

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

Enjoy using Backendless services!

Share this post
Tweet about this on TwitterShare on FacebookGoogle+