Skip to content

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:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setPropertiesWithProperties:@[@"Avg(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.setProperties(properties: ["Avg(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 avg property in the returned object. The value of the property is the mathematical average of all values in the totalBoxOffice column:

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

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:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setPropertiesWithProperties:@[@"Avg(totalBoxOffice) as averageTotalBoxOffice"]];

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.setProperties(properties: ["Avg(totalBoxOffice) as averageTotalBoxOffice"])

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 averageTotalBoxOffice property:

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

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:

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setPropertiesWithProperties:@[@"Avg(totalBoxOffice)", @"yearReleased"]];
[queryBuilder setGroupByGroupBy:@[@"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.setProperties(properties: ["Avg(totalBoxOffice)", "yearReleased"])
queryBuilder.setGroupBy(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 average earning values for the movies grouped by the release year:

(  
        {  
        "___class" = Movie;  
        "__meta" = "{\"selectedProperties\":[\"avg\",\"yearReleased\"]}";  
        avg = 2207615668;  
        objectId = "71F045B1-4A10-441A-FFF9-5005EF4FD400";  
        yearReleased = 1997;  
    },  
        {  
        "___class" = Movie;  
        "__meta" = "{\"selectedProperties\":[\"avg\",\"yearReleased\"]}";  
        avg = 2783918982;  
        objectId = "FEA0F8E2-44C0-3717-FFD9-28A7763E5500";  
        yearReleased = 2009;  
    },  
        {  
        "___class" = Movie;  
        "__meta" = "{\"selectedProperties\":[\"avg\",\"yearReleased\"]}";  
        avg = 1341511219;  
        objectId = "C1CF4388-96B0-6A37-FFAE-69A305E44100";  
        yearReleased = 2011;  
    },  
        {  
        "___class" = Movie;  
        "__meta" = "{\"selectedProperties\":[\"avg\",\"yearReleased\"]}";  
        avg = 1519479547;  
        objectId = "9B01F85E-BB31-84D9-FFF0-966F4CE78800";  
        yearReleased = 2012;  
    },  
        {  
        "___class" = Movie;  
        "__meta" = "{\"selectedProperties\":[\"avg\",\"yearReleased\"]}";  
        avg = 1913817556;  
        objectId = "A42CC872-BED0-8738-FF93-7326E1A38100";  
        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 setPropertiesWithProperties:@[@"Avg(totalBoxOffice)", @"yearReleased"]];
[queryBuilder setGroupByGroupBy:@[@"yearReleased"]];
[queryBuilder setSortBySortBy:@[@"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.setProperties(properties: ["Avg(totalBoxOffice)", "yearReleased"])
queryBuilder.setGroupBy(groupBy: ["yearReleased"])
queryBuilder.setSortBy(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 setPropertiesWithProperties:@[@"Avg(totalBoxOffice) as averageTotal", @"yearReleased"]];
[queryBuilder setGroupByGroupBy:@[@"yearReleased"]];
[queryBuilder setSortBySortBy:@[@"averageTotal"]];

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.setProperties(properties: ["Avg(totalBoxOffice) as averageTotal", "yearReleased"])
queryBuilder.setGroupBy(groupBy: ["yearReleased"])
queryBuilder.setSortBy(sortBy: ["averageTotal"])

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