COUNT¶
The Count aggregate function allows you to calculate the number of objects in a table, optionally satisfying some criteria or grouped by values in a column. To use the function, include Count(columnNameInDatabase) into the list of properties requested from the server. For example, the following query retrieves the count of all movies in the database:
DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.properties = @[@"Count(objectId)"];
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 = ["Count(objectId)"]
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 count property in the returned object. The value of the property is the total count of all objects in the table:
{
"___class" = Movie;
"__meta" = "{\"selectedProperties\":[\"count\"]}";
count = 8;
objectId = "71F045B1-4A10-441A-FFF9-5005EF4FD400";
}
Using Alias¶
To change the name of the returned property from count to a custom name, use the following syntax:
Count( columnNameInDatabase ) as customPropertyName
For example, the following query returns the total count value in the totalObjects property:
DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.properties = @[@"Count(objectId) as totalObjects"];
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 = ["Count(objectId) as totalObjects"]
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 totalObjects property:
{
"___class" = Movie;
"__meta" = "{\"selectedProperties\":[\"count\"]}";
totalObjects = 8;
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 number of movies in the database grouped by the year the movies were released:
DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
queryBuilder.properties = @[@"Count(objectId)", @"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 = ["Count(objectId)", "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 the number of the movies grouped by the release year:
(
{
"___class" = Movie;
"__meta" = "{\"selectedProperties\":[\"yearReleased\",\"count\"]}";
count = 1;
objectId = "71F045B1-4A10-441A-FFF9-5005EF4FD400";
yearReleased = 1997;
},
{
"___class" = Movie;
"__meta" = "{\"selectedProperties\":[\"yearReleased\",\"count\"]}";
count = 1;
objectId = "FEA0F8E2-44C0-3717-FFD9-28A7763E5500";
yearReleased = 2009;
},
{
"___class" = Movie;
"__meta" = "{\"selectedProperties\":[\"yearReleased\",\"count\"]}";
count = 1;
objectId = "C1CF4388-96B0-6A37-FFAE-69A305E44100";
yearReleased = 2011;
},
{
"___class" = Movie;
"__meta" = "{\"selectedProperties\":[\"yearReleased\",\"count\"]}";
count = 1;
objectId = "9B01F85E-BB31-84D9-FFF0-966F4CE78800";
yearReleased = 2012;
},
{
"___class" = Movie;
"__meta" = "{\"selectedProperties\":[\"yearReleased\",\"count\"]}";
count = 4;
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.properties = @[@"Count(objectId)", @"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 = ["Count(objectId)", "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 = @[@"Count(objectId) as movieCount", @"yearReleased"];
queryBuilder.groupBy = @[@"yearReleased"];
queryBuilder.sortBy = @[@"movieCount"];
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 = ["Count(objectId) as movieCount", "yearReleased"]
queryBuilder.groupBy = ["yearReleased"]
queryBuilder.sortBy = ["movieCount"]
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:
The example below counts the number of records stored in the movieName column. To calculate the number of records you must use the aggregate function Count(columnName) in the properties argument:
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 count property containing the number of records stored in the requested column/property:
Using Alias¶
Any default property returned in the response can have a custom name. You can change the default property name (e.g. count) returned in the response by defining a dynamic property as shown in the excerpt below. The movieName is the name of the column in the data table, and the numberOfMoviesInDatabase is the custom property name that will be shown in the response instead of the current property name (e.g. count):
Count(movieName) as numberOfMoviesInDatabase
The example below counts the number of values stored in the "movieName" column and returns the result with a custom property name "numberOfMoviesInDatabase":
After the Codeless logic runs, the operation returns an object with a new property name numberOfMoviesInDatabase:
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 counted 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 counts the total number.
The example below groups values in the movieName column/property by values in the yearReleased column/property into distinct objects and then counts the total. For better perception of the operation result you can also include the yearReleased in the properties argument, so that you could see the total number of movies for every year in the response.
The operation has returned a list of objects containing a total number of movies(movieName 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:
