Skip to content

SUM

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

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

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

[  
    {  
        "sum": 14507795640,  
        "___class": "Movie"  
    }  
]

Using Alias

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

Sum( columnNameInDatabase ) as customPropertyName

For example, the following query returns the sum value in the grandTotal property:

Important

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

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

The response for the query now contains the grandTotal property:

[  
    {  
        "grandTotal": 14507795640,  
        "___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 sum of 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=Sum(totalBoxOffice),yearReleased&groupBy=yearReleased

The response for this request contains sums of earnings for the movies grouped by the release year:

[  
    {  
        "sum": 2207615668,  
        "yearReleased": 1997,  
        "___class": "Movie",  
    },  
    {  
        "sum": 2783918982,  
        "yearReleased": 2009,  
        "___class": "Movie",  
    },  
    {  
        "sum": 1341511219,  
        "yearReleased": 2011,  
        "___class": "Movie",  
    },  
    {  
        "sum": 1519479547,  
        "yearReleased": 2012,  
        "___class": "Movie",  
    },  
    {  
        "sum": 6655270224,  
        "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=Sum(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=Sum(totalBoxOffice)%20as%20grandTotal,yearReleased&groupBy=yearReleased&sortBy=grandTotal