AVERAGE

The Avg aggregate function allows you to calculate the average value for a set of objects. To use the function, include Avg(columnNameInDatabase) into the list of properties requested from the server. For example, the following query retrieves an average box office earnings value for all movies in the database:

GET https://xxxx.backendless.app/api/data/Movie?props=Avg(totalBoxOffice) 

The query returns the following result, notice the avg property in the returned object. The value of the property is the mathematical average of all values in the totalBoxOffice column:

[  
    {  
        "avg": 1813474455,  
        "___class": "Movie",  
    }  
]

Using Alias

To change the name of the returned property from avg to a custom name, use the following syntax:

Avg( columnNameInDatabase ) as customPropertyName

For example, the following query returns the average value in the averageTotalBoxOffice property:

Important

The  value must be URL-encoded. The spaces are replaced with %20:

GET https://xxxx.backendless.app/api/data/Movie?props=Avg(totalBoxOffice)%20as%20averageTotalBoxOffice

The response for the query now contains the averageTotalBoxOffice property:

[  
    {  
        "averageTotalBoxOffice": 1813474455,  
        "___class": "Movie",  
    }  
]

Grouping Results

Results in the response can be grouped by values from another column. To request grouping of the results, add the groupBy parameter to the request with the value containing the name of the column. For example, the following request returns the average box office earnings value for the movies in the database grouped by the year the movies were released:

GET https://xxxx.backendless.app/api/data/Movie?props=Avg(totalBoxOffice),yearReleased&groupBy=yearReleased

The response for this request contains average earning values for the movies grouped by the release year:

[  
    {  
        "avg": 2207615668,  
        "yearReleased": 1997,  
        "___class": "Movie",  
    },  
    {  
        "avg": 2783918982,  
        "yearReleased": 2009,  
        "___class": "Movie",  
    },  
    {  
        "avg": 1341511219,  
        "yearReleased": 2011,  
        "___class": "Movie",  
    },  
    {  
        "avg": 1519479547,  
        "yearReleased": 2012,  
        "___class": "Movie",  
    },  
    {  
        "avg": 1663817556,  
        "yearReleased": 2015,  
        "___class": "Movie",  
    }  
]

Sorting

The results can be sorted using the sortBy parameter. For example, results for the following request will be sorted by the values in the yearReleased column in the descending order:

GET https://xxxx.backendless.app/api/data/Movie?props=Avg(totalBoxOffice),yearReleased&groupBy=yearReleased&sortBy=yearReleased%20DESC

To sort results by the aggregated value, assign a custom name to the column and sort by that name:

GET https://xxxx.backendless.app/api/data/Movie?props=Avg(totalBoxOffice)%20as%20averageTotal,yearReleased&groupBy=yearReleased&sortBy=averageTotal

Codeless Reference

Consider the following data table called Movie:

The example below calculates an average box office value earnings for all movies in the database. In the context of this example, the properties argument expects the aggregate function Avg(columnName) to make a query.

Important

For a detailed description of all input parameters see the Basic Object Retrieval topic of this guide.

After the Codeless logic runs the operation returns one object containing the average value:

Using Alias

Any default property returned in the response can have a custom name. You can change the default property name (e.g. avg) returned in the response by defining a dynamic property as shown in the excerpt below. The totalBoxOffice is the name of the column in the data table, and the averageTotalBoxOffice is the custom property name that will be shown in the response instead of the current property name (e.g. avg):

Avg(totalBoxOffice) as averageTotalBoxOffice

The example below calculates an average for all values stored in the "totalBoxOffice" column and returns the result with a custom property name "averageTotalBoxOffice":

After the Codeless logic runs, the operation returns an object with a new property name averageTotalBoxOffice:

Grouping The Results

Results can be grouped by values from another column. To group results you have to reference the name of the column in the group by property. By doing so, you instruct the operation to separate every calculated average value into a distinct object based on the value from the column you have used in the group by property.

As you reference the column name in the group by property, the operation groups all values first, and then it calculates an average:

The example below groups values in the totalBoxOffice column/property by values in the yearReleased column/property into distinct objects and calculates an average for every object. For better perception of the operation result you can also include the yearReleased in the properties argument, so that you can see an average value for every year in the response.

The operation has returned a list of objects containing an average value of total earnings(averageTotalBoxOffice property) grouped by the values in the yearReleased column. As you can see, every object is separated by values from the yearReleased column.

Sorting

You can sort the data by referencing the column/property name in the sort by property.

Results for the following request will be sorted by the values in the yearReleased column in the descending order:

As you can see, objects returned in the response are sorted in descending order by values in the yearReleased column/property: