Unlocking the Power of Aggregate Functions in FetchXML for Dynamics 365

In Dynamics 365, FetchXML is a key tool to get useful information from your data. It has a great feature that lets you do things like adding up sales, finding an average, or spotting the highest and lowest values. Let’s explore these features in FetchXML more closely.

Understanding Aggregate Functions in FetchXML

An aggregate function processes multiple rows of data to produce a single, summarized output. In FetchXML, aggregate functions are included within the <attribute> element, using the aggregate attribute.

1. The SUM Function

The sum function is used to add together the values of a specified attribute.

Usage Example: If you want to calculate the total sales for a specific period:

<fetch aggregate="true"> 
  <entity name="salesorder"> 
    <attribute name="totalamount" aggregate="sum" alias="sum_sales"/> 
  </entity> 
</fetch>

The result will provide you with the summed up totalamount for all sales orders.

2. The AVG Function

The avg function calculates the average value of a specified attribute.

Usage Example: To find the average discount given across all sales:

<fetch aggregate="true"> 
  <entity name="salesorder"> 
    <attribute name="discountamount" aggregate="avg" alias="average_discount"/> 
  </entity> 
</fetch>

3. The MIN Function

The min function identifies the smallest value within a specified attribute.

Usage Example: To find the lowest-priced product:

<fetch aggregate="true"> 
  <entity name="product"> 
    <attribute name="price" aggregate="min" alias="lowest_price"/> 
  </entity> 
</fetch>

4. The MAX Function

The max function identifies the highest value within a specified attribute.

Usage Example: To determine the most expensive service offered:

<fetch aggregate="true"> 
  <entity name="service"> 
    <attribute name="price" aggregate="max" alias="highest_price"/>
  </entity>
</fetch>

5. The COUNT(*) Function

The count(*) function counts all records of the specified entity.

Usage Example: To count the number of active contacts:

<fetch aggregate="true"> 
  <entity name="contact"> 
    <attribute name="contactid" aggregate="count" alias="total_contacts"/> 
  </entity> 
</fetch>

6. The COUNT(attribute name) Function

The count(attribute name) function counts non-null values of the specified attribute.

Usage Example: To count contacts that have an email address:

<fetch aggregate="true"> 
  <entity name="contact"> 
    <attribute name="emailaddress1" aggregate="count" alias="contacts_with_email"/> 
  </entity> 
</fetch>