FetchXML Tutotorial

Use aggregation in FetchXML

In this article, we will learn How to Use Use aggregation in FetchXML.

In this blog we will see, How to Use aggregation in FetchXML

In Common Data Service, FetchXML includes grouping and aggregation features where you can calculate sum, average min, max and count.

The following aggregate functions are supported in fetchxml:

1. sum
2. avg
3. min
4. max
5.count(*)
6.count(attribute name)

About aggregation
To create an aggregate attribute, set the keyword aggregate to true, then specify a valid entity name, attribute name and alias (variable name). It’s must also specify the type of aggregation you want to perform.

The fetchxml aggregate MIN() Functions
The MIN() function returns the smallest value of the selected column.
MIN() Syntax

<fetch mapping="logical" aggregate="true" version="1.0">
  <entity name="entty_name">
    <attribute name="attr_name" alias="alias_name" aggregate="min" />
  </entity>
</fetch>

Product Entity Record :
use-aggregation-fetchxml

MIN() Example
The following fetchxml statement finds the price of the cheapest product:

Example

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true" >
    <entity name="vnks_product" >
        <attribute name="vnks_price" alias="SmallestPrice" aggregate="min" />
    </entity>
</fetch>

Result :

<resultset morerecords="0" >
    <result>
        <SmallestPrice formattedvalue="1,200.00" >
            1200
        </SmallestPrice>
    </result>
</resultset>

The fetchxml aggregate MAX() Functions

The MAX() function returns the largest value of the selected column.

MAX() Syntax

<fetch mapping="logical" aggregate="true" version="1.0">
  <entity name="entty_name">
    <attribute name="attr_name" alias="alias_name" aggregate="max" />
  </entity>
</fetch>

MAX() Example
The following fetchxml statement finds the price of the most expensive product:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true" >
    <entity name="vnks_product" >
        <attribute name="vnks_price" alias="LargestPrice" aggregate="max" />
    </entity>
</fetch>

Result :

<resultset morerecords="0" >
    <result>
        <LargestPrice formattedvalue="12,000.00" >
            12000
        </LargestPrice>
    </result>
</resultset>

The fetchxml COUNT() Functions
The COUNT() function returns the number of rows that matches a specified criteria.

COUNT() Syntax:

<fetch mapping="logical" aggregate="true" version="1.0">
  <entity name="entity_name">
    <attribute name="attr_name" alias="alias_name" aggregate="countcolumn" />
  </entity>
</fetch>

COUNT() Example
The following fetchxml statement finds the number of products:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true" >
    <entity name="vnks_product" >
        <attribute name="vnks_productid" alias="COUNT_ProductID" aggregate="countcolumn" />
    </entity>
</fetch>

Result:

<resultset morerecords="0">
  <result>
    <COUNT_ProductID formattedvalue="6">6</COUNT_ProductID>
  </result>
</resultset>

The fetchxml AVG() Functions

The AVG() function returns the average value of a numeric column.

AVG() Syntax:

<fetch mapping="logical" aggregate="true" version="1.0">
  <entity name="entity_name">
    <attribute name="attrbute_name" alias="AVG_Price" aggregate="avg" />
  </entity>
</fetch>

AVG() Example
The following fetchxml statement finds the average price of all products:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true" >
    <entity name="vnks_product" >
        <attribute name="vnks_price" alias="AVG_Price" aggregate="avg" />
    </entity>
</fetch>

Result :

<resultset morerecords="0" >
    <result>
        <AVG_Price formattedvalue="5,950.00" >
            5950
        </AVG_Price>
    </result>
</resultset>

The fetchxml SUM() Functions

The SUM() function returns the total sum of a numeric column.

SUM() Syntax:

<fetch mapping="logical" aggregate="true" version="1.0">
  <entity name="entity_name">
    <attribute name="attribute_name" alias="SUM_Quantity" aggregate="sum" />
  </entity>
</fetch>

SUM() Example
The following fetchxml statement finds the sum of the “vnks_price” fields in the “Product” table:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true" >
    <entity name="vnks_product" >
        <attribute name="vnks_price" alias="SUM_price" aggregate="sum" />
    </entity>
</fetch>

Resullt:

<resultset morerecords="0" >
    <result>
        <SUM_price formattedvalue="35,700.00" >
            35700
        </SUM_price>
    </result>
</resultset>

Any question and suggestion related to this article Use aggregation in Fetchxml, please comment me Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *