Progress will discontinue Telerik Platform on May 10th, 2018. Learn more

Data Aggregation and Grouping

Data aggregation and grouping allow you to query for data more efficiently, offloading the processing to the server and decreasing bandwidth usage for your app. For example you could directly request the sum of a number of fields instead of first reading them and then summing them locally.

Using Aggregation and Grouping

To read aggregated or grouped data, create a data handler for the content type that you want to work with and then use the Aggregate() and GroupBy() methods to specify the kind of aggregation or grouping you want.

var groupedItems = app.WorkWith()
        .Data("type-name")
        .Aggregate()
        .GroupBy(new string[] {group-field-name1, <group-field-name2, ... group-field-nameN>})
        .<function1>(source-field-name1<, result-field-name1>)
        .<function2>(source-field-name2<, result-field-name2>)
            ...
        .<functionN>(source-field-nameN<, result-field-nameN>)
        .ExecuteSync();

Where:

  • group-field-nameN is a field name by which you want to group.
  • functionN is the name of the aggregation function.
  • source-field-nameN is the name of the field that you are aggregating.
  • result-field-nameN is the name that you want to give to the aggregated field. Optional. If you skip it, the result field name matches the source field name.

Aggregation Functions

The aggregate expression can contain any combination and number of the following aggregation functions:

Function Description
Sum() Returns the sum of all values in a content type field. The field type must be Number.
Max() Returns the maximum value found in a content type field. The field type must be Number or Date and Time.
Min() Returns the minimum value found in a content type field. The field type must be Number or Date and Time.
Avg() Returns the average of all values in a content type field. The field type must be Number.
Count() Returns the total count of records in a content type. If used in conjunction with grouping, it returns the record count for each group. In contrast to the other aggregation functions, you do not pass a source field.

The following example uses typed data and lambda expressions to demonstrate how to apply all aggregation functions in a single request.

// the result type is IEnumerable<BalanceAggregated>
// fields can be set as lambda functions
var requestResult = app.WorkWith()
                        .Data<Balance>()
                        .Aggregate<BalanceAggregated>()
                        .Sum(b => b.Income, a => a.TotalIncome)
                        .Max(b => b.Income, a => a.BiggestIncome)
                        .Min(b => b.Income, a => a.SmallestIncome)
                        .Avg(b => b.Income, a => a.AverageIncome)
                        .Count(a => a.NumberOfDeals)
                        .ExecuteSync();

// access the first item with the First() LINQ function
BalanceAggregated firstRow = requestResult.First();

// fields can be accessed as properties (or as a dictionary if the field is not declared in the class)
var totalIncome = firstRow.TotalIncome;
var maxIncome = firstRow.BiggestIncome;
var minIncome = firstRow.SmallestIncome;
var avgIncome = firstRow.AverageIncome;
var countIncome = firstRow.NumberOfDeals;

// Sample class declarations for the content type and the aggregated data used follow
public class Balance : DataItem
{
    public int Income
    {
        get
        {
            return this.income;
        }
        set
        {
            this.income = value;
            this.OnPropertyChanged("Income");
        }
    }
    private int income;
}

public class BalanceAggregated : AggregatedItem
{
    public int TotalIncome { get; set; }
    public int BiggestIncome { get; set; }
    public int SmallestIncome { get; set; }
    public double AverageIncome { get; set; }
    public int NumberOfDeals { get; set; }
}

You can also pass the field names as strings if you don't want to declare the aggregated data type. In this case the returned data can be accessed as a dictionary.

var contentTypeName = "Balance";
var sourceFieldName = "Income";
var sumFieldName = "TotalIncome";
var maxFieldName = "BiggestIncome";
var minFieldName = "SmallestIncome";
var avgFieldName = "AverageIncome";
var countFieldName = "NumberOfDeals";

// requestResult is of type IEnumerable<AggregatedItem>
var requestResult = app.WorkWith()
                        .Data(contentTypeName)
                        .Aggregate()
                        .Sum(sourceFieldName, sumFieldName)
                        .Max(sourceFieldName, maxFieldName)
                        .Min(sourceFieldName, minFieldName)
                        .Avg(sourceFieldName, avgFieldName)
                        .Count(countFieldName)
                        .ExecuteSync();

var firstRow = requestResult.First(); // access the first item with the First() LINQ function

// fields of AggregatedItem can be accessed as a dictionary
var totalIncome = firstRow[sumFieldName];
var maxIncome = firstRow[maxFieldName];
var minIncome = firstRow[minFieldName];
var avgIncome = firstRow[avgFieldName];
var countIncome = firstRow[countFieldName];

Using the sample data, the example would return the following:

TotalIncome would be 166000.

BiggestIncome would be 50000.

SmallestIncome would be 5000.

AverageIncome would be 16600.

NumberOfDeals would be 10.

Grouping

You can group aggregated data by a specified field or even multiple fields. To do this, use the GroupBy method to specify the group by fields.

If you want to count grouped data, use the Count aggregation function; the generic GetCount method is not supported on aggregated/grouped data.

var contentTypeName = "Balance";
var groupByField = "Client";
var targetCountField = "NumberOfDeals";
var sourceSumField = "Income";
var targetSumField = "TotalIncome";

var groupedItems = app.WorkWith()
                        .Data(contentTypeName)
                        .Aggregate()
                        .GroupBy(groupByField)
                        .Count(targetCountField)
                        .Sum(sourceSumField, targetSumField)
                        .ExecuteSync();

// extracting data from the result dataset
foreach (var row in groupedItems)
{
    //fields of AggregatedItem can be accessed as a dictionary
    Console.WriteLine(string.Format("Client: {0}, NumberOfDeals: {1}, TotalIncome: {2}", row[groupByField], row[targetCountField], row[targetSumField]));
}

Using the sample data, the example would print the following:

Client: The Nicest Pet Shop, NumberOfDeals: 5, TotalIncome: 66000
Client: Cute Animals Kingdom, NumberOfDeals: 3, TotalIncome: 45000
Client: Friendly Veterinary Shop, NumberOfDeals: 2, TotalIncome: 55000

Requesting Distinct Values

To get the unique values of a field, specify it in the GroupBy method but do not include any Aggregate functions.

var contentTypeName = "Balance";
var groupByField = "Client";

// requestResult is of type IEnumerable<AggregatedItem>
var distinctClients = app.WorkWith()
                        .Data(contentTypeName)
                         .Aggregate()
                         .GroupBy(groupByField)
                         .ExecuteSync();

// extracting data from the result dataset
foreach (var row in distinctClients)
{
    //fields of AggregatedItem can be accessed as a dictionary
    Console.WriteLine(string.Format("Client:{0}", row[groupByField]));
}

Using the sample data, the example would print the following:

Client: The Nicest Pet Shop
Client: Cute Animals Kingdom
Client: Friendly Veterinary Shop

In case you specify multiple grouping fields, the result contains the unique combinations of the fields' values.

var contentTypeName = "Balance";
var fieldNameCountry = "Country";
var fieldNameClient = "Client";
var groupByFields = new string[2] { fieldNameCountry, fieldNameClient };


// requestResult is of type IEnumerable<AggregatedItem>
var distinctLocations = app.WorkWith()
                           .Data(contentTypeName)
                           .Aggregate()
                           .GroupBy(groupByFields)
                           .ExecuteSync();

var firstRow = distinctLocations.First(); // access the first item with the First() LINQ function

// extracting data from the result dataset
foreach (var row in distinctLocations)
{
    //fields of AggregatedItem can be accessed as a dictionary
    Console.WriteLine(string.Format("country:{0}, Client:{1}", row[fieldNameCountry], row[fieldNameClient]));
}

Using the sample data, the example would print the following:

Country: USA, Client: The Nicest Pet Shop
Country: USA, Client: Cute Animals Kingdom
Country: Canada, Client: Friendly Veterinary Shop

Aggregating Filtered Data

You can apply aggregation functions to data that has been first reduced using a filtering expression. To to this, call the Where() method. Such queries are always first filtered, then aggregated.

using Telerik.Everlive.Sdk.Core.Query.Definition.Filtering.Simple;

var contentTypeName = "Balance";
var groupByField = "Client";
var targetCountField = "NumberOfDeals";
var sourceSumField = "Income";
var targetSumField = "TotalIncome";

Condition condition = new ValueCondition(sourceSumField, ValueConditionOperator.ValueGreaterThan, 15000);

var groupedItems = this.App.WorkWith()
                       .Data(contentTypeName)
                       .Aggregate()
                       .GroupBy(groupByField)
                       .Where(condition)
                       .Count(targetCountField)
                       .Sum(sourceSumField, targetSumField)
                       .ExecuteSync();

// extracting data from the result dataset
foreach (var row in groupedItems)
{
    //fields of AggregatedItem can be accessed as a dictionary
    Console.WriteLine(string.Format("Client:{0}, NumberOfDeals:{1}, TotalIncome:{2}", row[groupByField], row[targetCountField], row[targetSumField]));
}

Using the sample data, the example would print the following:

Client: The Nicest Pet Shop, NumberOfDeals: 2, TotalIncome: 38000
Client: Cute Animals Kingdom, NumberOfDeals: 1, TotalIncome: 25000
Client: Friendly Veterinary Shop, NumberOfDeals: 1, TotalIncome: 50000

Cloud Code Events

You can hook to a couple of Cloud Code events to make additional processing when aggregating or grouping data: beforeAggregate and afterAggregate. Find more information about using Cloud Code events, see Data Events for Cloud Code.

Sample Data Structure

The examples in this article are based on the following imaginary content type called Balance:

Client Country Income Products
The Nicest Pet Shop USA 12000 7
The Nicest Pet Shop USA 10000
Cute Animals Kingdom USA 7000 5, 4, 1, 7
Friendly Veterinary Shop Canada 5000 2, 3
Cute Animals Kingdom USA 13000 5, 1
The Nicest Pet Shop USA 20000
Friendly Veterinary Shop Canada 50000 7, 6, 5, 4
The Nicest Pet Shop USA 18000 4, 5
Cute Animals Kingdom USA 25000 1, 6
The Nicest Pet Shop USA 6000 1, 2, 6

Limitations

  • Using additional query options such as filtering, paging, sorting, etc. is not supported on aggregated/grouped data.
  • Aggregation and grouping on content types from Data Connectors is not supported.
  • Aggregation and grouping on Files is not supported.
  • Kendo UI DataSource:
    • serverAggregates: true is supported; ensure that serverFiltering is also set to true.
    • serverGrouping is not supported.
  • Aggregation and grouping are allowed on no more than 500,000 items. To ensure that you stay within that number, always filter down your data to a more representative data set before running any aggregation functions or grouping.

See Also


Contact us: +1-888-365-2779
sales@telerik.com
Copyright © 2016-2017, Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.