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.Create();
queryBuilder.AddProperty( "Count(objectId)" );

// ***********************************************************
// Blocking API:
// ***********************************************************
IList<Dictionary<string, object>> result;
result = Backendless.Data.Of( "Movie" ).Find( queryBuilder );

// ***********************************************************
// Non-blocking API:
// ***********************************************************
AsyncCallback<IList<Dictionary<string, object>>> findCallback;
findCallback = new AsyncCallback<IList<Dictionary<string, object>>>(
  aggrResult =>
  {
    // print out the first object from the collection
    System.Console.WriteLine( aggrResult[ 0 ] );
  },
  error =>
  {
    System.Console.WriteLine( "Server returned an error " + error.Message );
  } );

Backendless.Data.Of( "Movie" ).Find( queryBuilder, findCallback );

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:

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.Create();
queryBuilder.AddProperty( "Count(objectId) as totalObjects" );

// ***********************************************************
// Blocking API:
// ***********************************************************
IList<Dictionary<string, object>> result;
result = Backendless.Data.Of( "Movie" ).Find( queryBuilder );

// ***********************************************************
// Non-blocking API:
// ***********************************************************
AsyncCallback<IList<Dictionary<string, object>>> findCallback;
findCallback = new AsyncCallback<IList<Dictionary<string, object>>>(
  aggrResult =>
  {
    // print out the first object from the collection
    System.Console.WriteLine( aggrResult[ 0 ] );
  },
  error =>
  {
    System.Console.WriteLine( "Server returned an error " + error.Message );
  } );

Backendless.Data.Of( "Movie" ).Find( queryBuilder, findCallback );

The response for the query now contains the totalObjects property:

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.Create();
queryBuilder.AddProperty( "Count(objectId)" );
queryBuilder.AddProperty( "yearReleased" );
queryBuilder.AddGroupBy( "yearReleased" );

// ***********************************************************
// Blocking API:
// ***********************************************************
IList<Dictionary<string, object>> result;
result = Backendless.Data.Of( "Movie" ).Find( queryBuilder );

// ***********************************************************
// Non-blocking API:
// ***********************************************************
AsyncCallback<IList<Dictionary<string, object>>> findCallback;
findCallback = new AsyncCallback<IList<Dictionary<string, object>>>(
  aggrResult =>
  {
    // print out the first object from the collection
    System.Console.WriteLine( aggrResult[ 0 ] );
  },
  error =>
  {
    System.Console.WriteLine( "Server returned an error " + error.Message );
  } );

Backendless.Data.Of( "Movie" ).Find( queryBuilder, findCallback );

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

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.Create();
queryBuilder.AddProperty( "Count(objectId)" );
queryBuilder.AddProperty( "yearReleased" );
queryBuilder.AddSortBy( "yearReleased DESC" );

// ***********************************************************
// Blocking API:
// ***********************************************************
IList<Dictionary<string, object>> result;
result = Backendless.Data.Of( "Movie" ).Find( queryBuilder );

// ***********************************************************
// Non-blocking API:
// ***********************************************************
AsyncCallback<IList<Dictionary<string, object>>> findCallback;
findCallback = new AsyncCallback<IList<Dictionary<string, object>>>(
  aggrResult =>
  {
    // print out the first object from the collection
    System.Console.WriteLine( aggrResult[ 0 ] );
  },
  error =>
  {
    System.Console.WriteLine( "Server returned an error " + error.Message );
  } );

Backendless.Data.Of( "Movie" ).Find( queryBuilder, findCallback );

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

DataQueryBuilder queryBuilder = DataQueryBuilder.Create();
queryBuilder.AddProperty( "Count(objectId) as movieCount" );
queryBuilder.AddProperty( "yearReleased" );
queryBuilder.AddSortBy( "movieCount" );

// ***********************************************************
// Blocking API:
// ***********************************************************
IList<Dictionary<string, object>> result;
result = Backendless.Data.Of( "Movie" ).Find( queryBuilder );

// ***********************************************************
// Non-blocking API:
// ***********************************************************
AsyncCallback<IList<Dictionary<string, object>>> findCallback;
findCallback = new AsyncCallback<IList<Dictionary<string, object>>>(
  aggrResult =>
  {
    // print out the first object from the collection
    System.Console.WriteLine( aggrResult[ 0 ] );
  },
  error =>
  {
    System.Console.WriteLine( "Server returned an error " + error.Message );
  } );

Backendless.Data.Of( "Movie" ).Find( queryBuilder, findCallback );