Multiple group by - FetchXML Query

The following FetchXML shows how to use the aggregate attribute and multiple groupby clauses.

<fetch distinct='false' mapping='logical' aggregate='true'>
   <entity name='opportunity'>
      <attribute name='opportunityid' alias='opportunity_count' aggregate='count'/>
      <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/>
      <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg'/>
      <attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />
      <attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />
      <filter type='and'>
         <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity>
</fetch>

Explanation

To create multiple group by FetchXML query:

• Set aggregate to true in fetch node.

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

• Add required aggregate attributes in entity clause.

  <attribute name='name' alias='opportunity_count' aggregate='count' />
  

• Add multiple group by attributes by setting keyword groupby to true.

  <attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />

  <attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />

- See more at: http://msxrmtools.com/fetchxml/reference