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
[php]
<fetch mapping="logical" aggregate="true" version="1.0">
<entity name="entty_name">
<attribute name="attr_name" alias="alias_name" aggregate="min" />
</entity>
</fetch>
[/php]
Product Entity Record :
MIN() Example
The following fetchxml statement finds the price of the cheapest product:
Example
[php]
<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>
[/php]
Result :
[php]
<resultset morerecords="0" >
<result>
<SmallestPrice formattedvalue="1,200.00" >
1200
</SmallestPrice>
</result>
</resultset>
[/php]
The fetchxml aggregate MAX() Functions
The MAX() function returns the largest value of the selected column.
MAX() Syntax
[php]
<fetch mapping="logical" aggregate="true" version="1.0">
<entity name="entty_name">
<attribute name="attr_name" alias="alias_name" aggregate="max" />
</entity>
</fetch>
[/php]
MAX() Example
The following fetchxml statement finds the price of the most expensive product:
[php]
<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>
[/php]
Result :
[php]
<resultset morerecords="0" >
<result>
<LargestPrice formattedvalue="12,000.00" >
12000
</LargestPrice>
</result>
</resultset>
[/php]
The fetchxml COUNT() Functions
The COUNT() function returns the number of rows that matches a specified criteria.
COUNT() Syntax:
[php]
<fetch mapping="logical" aggregate="true" version="1.0">
<entity name="entity_name">
<attribute name="attr_name" alias="alias_name" aggregate="countcolumn" />
</entity>
</fetch>
[/php]
COUNT() Example
The following fetchxml statement finds the number of products:
[php]
<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>
[/php]
Result:
[php]
<resultset morerecords="0">
<result>
<COUNT_ProductID formattedvalue="6">6</COUNT_ProductID>
</result>
</resultset>
[/php]
The fetchxml AVG() Functions
The AVG() function returns the average value of a numeric column.
AVG() Syntax:
[php]
<fetch mapping="logical" aggregate="true" version="1.0">
<entity name="entity_name">
<attribute name="attrbute_name" alias="AVG_Price" aggregate="avg" />
</entity>
</fetch>
[/php]
AVG() Example
The following fetchxml statement finds the average price of all products:
[php]
<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>
[/php]
Result :
[php]
<resultset morerecords="0" >
<result>
<AVG_Price formattedvalue="5,950.00" >
5950
</AVG_Price>
</result>
</resultset>
[/php]
The fetchxml SUM() Functions
The SUM() function returns the total sum of a numeric column.
SUM() Syntax:
[php]
<fetch mapping="logical" aggregate="true" version="1.0">
<entity name="entity_name">
<attribute name="attribute_name" alias="SUM_Quantity" aggregate="sum" />
</entity>
</fetch>
[/php]
SUM() Example
The following fetchxml statement finds the sum of the “vnks_price” fields in the “Product” table:
[php]
<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>
[/php]
Resullt:
[php]
<resultset morerecords="0" >
<result>
<SUM_price formattedvalue="35,700.00" >
35700
</SUM_price>
</result>
</resultset>
[/php]
If This article helped you. Humble request to you please donate small amount only 5 Ruppes or 1 Ruppes so that I can help the needy poor people. Send amount this phone upi id : 8800846247@ybl or google pay 8800846247. Thank you from my bottom of heart.
Any question and suggestion related to this article Use aggregation in Fetchxml, please comment me Thank you.
