Düzenle

Aracılığıyla paylaş

Aggregate data using QueryExpression

  • Makale

QueryExpression includes grouping and aggregation features that let you calculate sum, average, min, max, and count across multiple rows of data.

To return an aggregate value, you must:

Types of aggregation

The types of aggregation you can do are members of the XrmAggregateType enum.

XrmAggregateType member Description
Avg The average value of the column values with data.
Count The number of rows.
CountColumn The number of rows with data in that column.
Max The maximum value of the rows in that column.
Min The minimum value of the rows in that column.
None Use this when the column is used to group the aggregated data. The HasGroupBy value must be set to true.
Sum The total value of the column values with data.

Note the following points:

  • Null values aren't considered when calculating aggregate values.
  • You can use data from tables joined using the LinkEntity class.
  • You can apply filters to limit the results as with any query.

Example

Let's say you have 10 account records with the following data:

Number of Employees Name Address 1 City Created On
NULL Example Account NULL 8/25/2023
1,500 Contoso Pharmaceuticals (sample) Redmond 3/25/2023
2,700 Fabrikam, Inc. (sample) Lynnwood 3/25/2023
2,900 Blue Yonder Airlines (sample) Los Angeles 3/25/2023
2,900 City Power & Light (sample) Redmond 3/25/2023
3,900 Coho Winery (sample) Phoenix 3/25/2023
4,300 Adventure Works (sample) Santa Cruz 3/25/2023
4,800 Alpine Ski House (sample) Missoula 3/25/2023
6,000 Litware, Inc. (sample) Dallas 3/25/2023
6,200 A. Datum Corporation (sample) Redmond 3/25/2023

The following query returns aggregate data for the numberofemployees column.

QueryExpression query = new()
{
    EntityName = "account",
    ColumnSet = new ColumnSet(false)
    {
        AttributeExpressions = {
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Average",
                    aggregateType: XrmAggregateType.Avg)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Count",
                    aggregateType: XrmAggregateType.Count)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "ColumnCount",
                    aggregateType: XrmAggregateType.CountColumn)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Maximum",
                    aggregateType: XrmAggregateType.Max)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Minimum",
                    aggregateType: XrmAggregateType.Min)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Sum",
                    aggregateType: XrmAggregateType.Sum)
            }
        }
    }
};

The results are a single row:

 --------------------------------------------------------------
 | Average | Count | ColumnCount | Maximum | Minimum | Sum    |
 --------------------------------------------------------------
 | 3,911   | 10    | 9           | 6,200   | 1,500   | 35,200 |
 --------------------------------------------------------------

Grouping

Group the results of an aggregate query by adding an XrmAttributeExpression with the HasGroupBy property AggregateType set to XrmAggregateType.None.

When grouping, you should specify a QueryExpression.Orders with an OrderExpression that has the Alias property set to the alias of the group.

For example the following query returns the sum of employees, and count by city:

QueryExpression query = new()
{
      EntityName = "account",
      ColumnSet = new ColumnSet(false)
   {
         AttributeExpressions = {
            {
                  new XrmAttributeExpression(
                     attributeName: "numberofemployees",
                     alias: "Total",
                     aggregateType: XrmAggregateType.Sum)
            },
            {
                  new XrmAttributeExpression(
                     attributeName: "address1_city",
                     alias: "Count",
                     aggregateType: XrmAggregateType.Count)
            },
            {
                  new XrmAttributeExpression(
                     attributeName: "address1_city",
                     alias: "City",
                     aggregateType: XrmAggregateType.None){
                     HasGroupBy = true
            }
         }
      }
   }
};
query.Orders.Add(new OrderExpression(
            attributeName: "address1_city",
            alias: "City",
            orderType: OrderType.Ascending));

The query groups the results by City value, combining the results for the three rows where city is 'Redmond'.

Total Count City
0 1 NULL
6,000 1 Dallas
2,900 1 Los Angeles
2,700 1 Lynnwood
4,800 1 Missoula
3,900 1 Phoenix
10,600 3 Redmond
4,300 1 Santa Cruz

Grouping by parts of a date

You can select which part of the date to use when grouping by date. Set the XrmAttributeExpression.DateTimeGrouping to one of the XrmDateTimeGrouping enum members.

XrmDateTimeGrouping member Description
None No DateTime grouping
Day Group by Day
Week Group by Week
Month Group by Month
Quarter Group by Quarter
Year Group by Year
FiscalPeriod Group by FiscalPeriod
FiscalYear Group by FiscalYear

The following query groups account records showing number of employees by when the records were created:

QueryExpression query = new()
{
    EntityName = "account",
    ColumnSet = new ColumnSet(false)
    {
        AttributeExpressions = {
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Total",
                    aggregateType: XrmAggregateType.Sum)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Day",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Day
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Week",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Week
                }
            },
                                    {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Month",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Month
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Year",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Year
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "FiscalPeriod",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.FiscalPeriod
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "FiscalYear",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.FiscalYear
                }
            }
        }
    }
};
query.Orders.Add(new OrderExpression(
            attributeName: "createdon",
            alias: "Month",
            orderType: OrderType.Ascending));

The following table shows the result using the example data set mentioned previously:

 -----------------------------------------------------------------------
 | Total  | Day | Week | Month | Year  | FiscalPeriod     | FiscalYear |
 -----------------------------------------------------------------------
 | 35,200 | 25  | 12   | 3     | 2,023 | Quarter 1 FY2023 | FY2023     |
 -----------------------------------------------------------------------
 | 0      | 27  | 35   | 8     | 2,023 | Quarter 3 FY2023 | FY2023     |
 -----------------------------------------------------------------------

Fiscal period date grouping example

The following example shows a QueryExpression that sums the total number of orders fulfilled and groups the result by fiscal semester and fiscal year.

QueryExpression query = new()
{
    EntityName = "salesorder",
    ColumnSet = new ColumnSet(false)
    {
        AttributeExpressions = {
            {
                new XrmAttributeExpression(
                    attributeName: "totalamount",
                    alias: "total",
                    aggregateType: XrmAggregateType.Sum)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "datefulfilled",
                    alias: "date",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.FiscalPeriod
                }
            }
        }
    }
};
query.Orders.Add(new OrderExpression(
            attributeName: "datefulfilled",
            alias: "Date",
            orderType: OrderType.Ascending));

QueryExpression aggregation limitations

This section describes capabilities that are available using aggregation with FetchXml that are not currently available using QueryExpression.

Get distinct number with CountColumn

You can't get a distinct number of values using CountColumn with QueryExpression. Learn about distinct column values using FetchXml

Time zone when grouping by date

Grouping by parts of a date always uses UTC time and there is no way to specify that the user's time zone should be used instead available in FetchXml

Row aggregate

When a table has a hierarchical relationship defined, you can't return a row aggregate on the lookup column for the hierarchical relationship. Learn about row aggregates using FetchXml

Per query limit

There is no way to specify a configurable aggregate limit. Learn about per query limits using FetchXml

Limitations

Queries that return aggregate values are limited to 50,000 records. This limit helps maintain system performance and reliability. If the filter criteria in your query returns more than 50,000 records, you get the following error:

Number: -2147164125
Code: 8004E023
Message: AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Client error message: The maximum record limit is exceeded. Reduce the number of records.

To avoid this error, add appropriate filters to your query to make sure it doesn't evaluate more than 50,000 records. Then run your query multiple times and combine the results. Appropriate filters depend on the nature of your data, but they could be a date range or a subset of values in a choice column.

Next steps

Learn how to count rows.

Count rows