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.Create();
queryBuilder.AddProperty( "Avg(totalBoxOffice)" );
// ***********************************************************
// 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 );
avg
property in the returned object. The value of the property is the mathematical average of all values in the totalBoxOffice
column: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.Create();
queryBuilder.AddProperty( "Avg(totalBoxOffice) as averageTotalBoxOffice" );
// ***********************************************************
// 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 );
averageTotalBoxOffice
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 average box office earnings value for the movies in the database grouped by the year the movies were released:
DataQueryBuilder queryBuilder = DataQueryBuilder.Create();
queryBuilder.AddProperty( "Avg(totalBoxOffice)" );
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 );
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( "Avg(totalBoxOffice)" );
queryBuilder.AddProperty( "yearReleased" );
queryBuilder.AddGroupBy( "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 );
DataQueryBuilder queryBuilder = DataQueryBuilder.Create();
queryBuilder.AddProperty( "Avg(totalBoxOffice) as averageTotal" );
queryBuilder.AddProperty( "yearReleased" );
queryBuilder.AddGroupBy( "averageTotal" );
// ***********************************************************
// 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 );