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:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.properties = @[@"Sum(totalBoxOffice)"];

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Movie"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundObjects) {
    // print out the first object from the collection
    NSLog(@"%@", foundObjects.firstObject);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
let queryBuilder = DataQueryBuilder()
queryBuilder.properties = ["Sum(totalBoxOffice)"]

let dataStore = Backendless.shared.data.ofTable("Movie")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundObjects in
    // print out the first object from the collection
    if let first = foundObjects.first {
        print("\(first)")
    }
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

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:

{  
    "___class" = Movie;  
    "__meta" = "{\"selectedProperties\":[\"sum\"]}";  
    objectId = "71F045B1-4A10-441A-FFF9-5005EF4FD400";  
    sum = 15507795640;  
}

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:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.properties = @[@"Sum(totalBoxOffice) as grandTotal"];

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Movie"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundObjects) {
    // print out the first object from the collection
       NSLog(@"%@", foundObjects.firstObject);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
let queryBuilder = DataQueryBuilder()
queryBuilder.properties = ["Sum(totalBoxOffice) as grandTotal"]

let dataStore = Backendless.shared.data.ofTable("Movie")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundObjects in
    // print out the first object from the collection
       if let first = foundObjects.first {
        print("\(first)")
    }
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

The response for the query now contains the grandTotal property:

{  
    "___class" = Movie;  
    "__meta" = "{\"selectedProperties\":[\"sum\"]}";  
    objectId = "71F045B1-4A10-441A-FFF9-5005EF4FD400";  
    grandTotal = 15507795640;  
}

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:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.properties = @[@"Sum(totalBoxOffice)", @"yearReleased"];
queryBuilder.groupBy = @[@"yearReleased"];

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Movie"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundObjects) {
    NSLog(@"%@", foundObjects);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
let queryBuilder = DataQueryBuilder()
queryBuilder.properties = ["Sum(totalBoxOffice)", "yearReleased"]
queryBuilder.groupBy = ["yearReleased"]

let dataStore = Backendless.shared.data.ofTable("Movie")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundObjects in
    print("\(foundObjects)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

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

(  
        {  
        "___class" = Movie;  
        "__meta" = "{\"selectedProperties\":[\"yearReleased\",\"sum\"]}";  
        objectId = "71F045B1-4A10-441A-FFF9-5005EF4FD400";  
        sum = 2207615668;  
        yearReleased = 1997;  
    },  
        {  
        "___class" = Movie;  
        "__meta" = "{\"selectedProperties\":[\"yearReleased\",\"sum\"]}";  
        objectId = "FEA0F8E2-44C0-3717-FFD9-28A7763E5500";  
        sum = 2783918982;  
        yearReleased = 2009;  
    },  
        {  
        "___class" = Movie;  
        "__meta" = "{\"selectedProperties\":[\"yearReleased\",\"sum\"]}";  
        objectId = "C1CF4388-96B0-6A37-FFAE-69A305E44100";  
        sum = 1341511219;  
        yearReleased = 2011;  
    },  
        {  
        "___class" = Movie;  
        "__meta" = "{\"selectedProperties\":[\"yearReleased\",\"sum\"]}";  
        objectId = "9B01F85E-BB31-84D9-FFF0-966F4CE78800";  
        sum = 1519479547;  
        yearReleased = 2012;  
    },  
        {  
        "___class" = Movie;  
        "__meta" = "{\"selectedProperties\":[\"yearReleased\",\"sum\"]}";  
        objectId = "A42CC872-BED0-8738-FF93-7326E1A38100";  
        sum = 7655270224;  
        yearReleased = 2015;  
    }  
)

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:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.properties = @[@"Sum(totalBoxOffice)", @"yearReleased"];
queryBuilder.groupBy = @[@"yearReleased"];
queryBuilder.sortBy = @[@"yearReleased DESC"];

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Movie"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundObjects) {
    NSLog(@"%@", foundObjects);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
let queryBuilder = DataQueryBuilder()
queryBuilder.properties = ["Sum(totalBoxOffice)", "yearReleased"]
queryBuilder.groupBy = ["yearReleased"]
queryBuilder.sortBy = ["yearReleased DESC"]

let dataStore = Backendless.shared.data.ofTable("Movie")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundObjects in
    print("\(foundObjects)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

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

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.properties = @[@"Sum(totalBoxOffice) as grandTotal", @"yearReleased"];
queryBuilder.groupBy = @[@"yearReleased"];
queryBuilder.sortBy = @[@"grandTotal"];

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Movie"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundObjects) {
    NSLog(@"%@", foundObjects);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
let queryBuilder = DataQueryBuilder()
queryBuilder.properties = ["Sum(totalBoxOffice) as grandTotal", "yearReleased"]
queryBuilder.groupBy = ["yearReleased"]
queryBuilder.sortBy = ["grandTotal"]

let dataStore = Backendless.shared.data.ofTable("Movie")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundObjects in
    print("\(foundObjects)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

Codeless Reference

Consider the following data table called Movie:

data_service_count_example

The example below calculates the sum of values stored in the totalBoxOffice column. To calculate the sum of values you must use the aggregate function Sum(columnName) in the properties argument:

data_service_sum_example_1

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 an object with the sum property containing the value which reflects the sum of all values stored in the requested column/property:

data_service_sum_example_2

Using Alias

Any default property returned in the response can have a custom name. You can change the default property name (e.g. sum) 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 boxOfficeAllMovies is the custom property name that will be shown in the response instead of the current property name (e.g. sum):

Sum(totalBoxOffice) as boxOfficeAllMovies

The example below calculates the sum of values stored in the "totalBoxOffice" column and returns the result with a custom property name "boxOfficeAllMovies":

data_service_sum_example_3

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

data_service_sum_example_4

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 sum 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 the sum.

The example below groups values in the totalBoxOffice column by values in the yearReleased column into distinct objects and then calculates the sum. For better perception of the operation result you can also include the yearReleased column in the properties argument, so that you could see the sum of the box office earnings for every year in the response.

data_service_sum_example_5

First, the operation has grouped all records into distinct objects based on the values in the yearReleased column passed to the group by property. Then for every object the operation has calculated the sum of values stored in the totalBoxOffice column.

data_service_sum_example_6

Sorting

You can sort the data by referencing the column 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:

data_service_sum_example_7

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

data_service_sum_example_8