Skip to content

Latest commit

 

History

History
262 lines (213 loc) · 8.65 KB

aggregate-data.md

File metadata and controls

262 lines (213 loc) · 8.65 KB
Raw
title description ms.date ms.reviewer ms.topic author ms.subservice ms.author search.audienceType contributors
Aggregate data using FetchXml
Learn how to use FetchXml to retrieve aggregated data from Microsoft Dataverse.
02/29/2024
jdaly
how-to
pnghub
dataverse-developer
gned
developer
JimDaly
dmitmikh
dasussMS
apahwa-lab
DonaldlaGithub

Aggregate data using FetchXml

FetchXML 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:

Note the following points:

  • Null values aren't considered when calculating aggregate values.
  • You can use data from tables joined using the link-entity element.
  • 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.

<fetch aggregate='true'>
  <entity name='account'>
    <attribute name='numberofemployees'
      alias='Average'
      aggregate='avg' />
    <attribute name='numberofemployees'
      alias='Count'
      aggregate='count' />
    <attribute name='numberofemployees'
      alias='ColumnCount'
      aggregate='countcolumn' />
    <attribute name='numberofemployees'
      alias='Maximum'
      aggregate='max' />
    <attribute name='numberofemployees'
      alias='Minimum'
      aggregate='min' />
    <attribute name='numberofemployees'
      alias='Sum'
      aggregate='sum' />
  </entity>
</fetch>

The results are a single row:

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

Distinct column values

When using the countcolumn aggregate function, you can set the distinct attribute to return a count of unique values for the column.

<attribute name='numberofemployees' 
   alias='ColumnCount' 
   aggregate='countcolumn' 
   distinct='true' />

When set for the previous query, the results return 8 rather than 9 because two rows in the data set have 2,900 as the number of employees value.

Grouping

Group the results of an aggregate query by adding an attribute element with the groupby attribute rather than the aggregate attribute. When grouping, you should specify an order element with an alias value set to the alias of the group.

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

<fetch aggregate='true'>
   <entity name='account'>
      <attribute name='numberofemployees'
         alias='Total'
         aggregate='sum' />
      <attribute name='address1_city'
         alias='Count'
         aggregate='count' />
      <attribute name='address1_city'
         alias='City'
         groupby='true' />
      <order alias='City' />
   </entity>
</fetch>

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 attribute element dategrouping attribute to one of the following values:

[!INCLUDE dategrouping-table]

By default date groupings use the user's time zone. Set the attribute element usertimezone attribute to "false" to specify that the UTC time zone be used instead.

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

<fetch aggregate='true'>
   <entity name='account'>
      <attribute name='numberofemployees'
         alias='Total'
         aggregate='sum' />
      <attribute name='createdon'
         alias='Day'
         groupby='true'
         dategrouping='day' />
      <attribute name='createdon'
         alias='Week'
         groupby='true'
         dategrouping='week' />
      <attribute name='createdon'
         alias='Month'
         groupby='true'
         dategrouping='month' />
      <attribute name='createdon'
         alias='Year'
         groupby='true'
         dategrouping='year' />
      <attribute name='createdon'
         alias='FiscalPeriod'
         groupby='true'
         dategrouping='fiscal-period' />
      <attribute name='createdon'
         alias='FiscalYear'
         groupby='true'
         dategrouping='fiscal-year' />
      <order alias='Month' />
   </entity>
</fetch>

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 FetchXML aggregation expression that sums the total number of orders fulfilled and groups the result by fiscal semester and fiscal year.

<fetch aggregate="true">
   <entity name="order">
      <attribute name="totalamount"
         aggregate="sum"
         alias="total" />
      <attribute name="datefulfilled"
         groupby="true"
         dategrouping="fiscal-period" />
   </entity>
</fetch>

Row aggregate

When a table has a hierarchical relationship defined, you can return a row aggregate on the lookup column for the hierarchical relationship.

The following example returns the number of related accounts in a column named CountChildren when the child account records parentaccountid column equals the current account accountid column.

<fetch top='5'>
   <entity name='account'>
      <attribute name='name' />
      <attribute name='accountid'
         alias='numberOfChildren'
         rowaggregate='CountChildren' />
      <order attribute='accountid'
         descending='true' />
   </entity>
</fetch>

[!INCLUDE cc-query-aggregation-limitations]

Per query limit

Even with the default limit for aggregate queries applied, the query might take some time to complete. You can use the aggregatelimit attribute in a query to apply a custom lower limit that returns the AggregateQueryRecordLimit exceeded error if the results are higher than your custom limit.

In this example, the custom maximum rows limit is 10:

<fetch aggregate='true'
   aggregatelimit = '10'>
   <entity name='opportunity'>
      <attribute name='name'
         alias='opportunity_count'
         aggregate='count' />
   </entity>
</fetch>

The per query limit can't exceed default aggregate limit.

Next steps

Learn how to count rows.

[!div class="nextstepaction"] Count rows

[!INCLUDE footer-banner]