Group by Using FetchXml in MSCRM

In Microsoft Dynamics CRM 2011 FetchXML includes grouping and aggregation features which let us to use aggregate functions like sum, count etc.

Using Group by we can calculate sum, avg, min, max, count. But, Group by clause is not supported through LINQ in CRM.

You can only specify one aggregate attribute in a query and you cannot use the distinct keyword. To create an aggregate attribute, set the keyword aggregate to true, then specify valid entity name, attribute name and alias(variable name). You must also specify the type of aggregation you want to perform.

<fetch distinct=’false’ mapping=’logical’ aggregate=’true’>

<entity name=’entity name’>

<attribute name=’attribute name’ aggregate=’count’ alias=’alias name’/>

</entity>

</fetch>”

Below is the example to get sum of total amount of all won quotes:

string quotes = @”

<fetch distinct=’false’ mapping=’logical’ aggregate=’true’>

<entity name=’quote’>

<attribute name=’totalamount’ alias=’totalamount_sum’ aggregate=’sum’/>

<attribute name=’statecode’ groupby=’true’ alias=’state’ />

<filter type=’and’>

<condition attribute=’ statecode ‘ operator=’eq’ value=’won’ />”+

“</filter> “+

“</entity> “+

“</fetch>”;

EntityCollection quotes_result = _service.RetrieveMultiple(new FetchExpression(quotes));

foreach (var q in quotes_result.Entities)

{

Decimal wonQuoteAmount = ((Money)((AliasedValue)q["totalamount_sum"]).Value).Value;

}