Welcome to MSDN Blogs Sign in | Join | Help

Support for COUNT(DISTINCT) in Fetch XML queries

We have made a couple of enhancements to the aggregate query feature of Fetch XML. These updates have shipped for version 4.0 as part of UR2 and for version 3.0 as documented in KB Article 960079. So far we only supported the count aggregate function which essentially did a count(*) on the base table of the entity.

We have enhanced this functionality to add a new aggregate operation countcolumn which now performs the SQL count operation for a given column. It also support the distinct keyword which only counts unique columns. If the distinct keyword is not specified with a countcolumn aggregate, its value defaults to false.

Here is a table that summarizes the Fetch aggregate types and its equivalent SQL translation:

CRM Fetch XML

SQL

<attribute name='address1_city' aggregate='count' alias='citycount' />

SELECT COUNT(*) AS 'citycount' FROM Account

<attribute name='address1_city' aggregate='countcolumn' alias='citycount' />

SELECT COUNT(address1_city) AS 'citycount' FROM Account

<attribute name='address1_city' aggregate='countcolumn' alias='citycount' distinct='true' />

SELECT COUNT(DISTINCT address1_city) AS 'citycount' FROM Account

                                                     Key: Existing Feature, New Feature

Here are a couple of examples for using these features:

Query to return a count of all Accounts which have a county specified in their address.

   1: Query to return a count of all Accounts which have a county specified in their address.
   2: <fetch aggregate='true'>
   3:     <entity name='account'>
   4:         <attribute name='address1_county' aggregate='countcolumn' alias='countyAccounts' />
   5:     </entity>
   6: </fetch>

Query to return a count of states in which we have an account. This uses the distinct keyword which counts a state only once.

   1: <fetch aggregate='true'>
   2:     <entity name='account'>
   3:         <attribute name='address1_stateorprovince' aggregate='countcolumn' alias='countStates' distinct='true' />
   4:     </entity>
   5: </fetch>

Thanks

Noor Merchant

 

Published Tuesday, March 10, 2009 9:19 AM by crmblog

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# re: Support for COUNT(DISTINCT) in Fetch XML queries

Nice enhancement. However, why does FetchXML not support average, sum or stdev? The FetchXML xsd in the CRM 4.0 SDK states that it does:

<xs:attribute name="aggregate">

  <xs:simpleType>

     <xs:restriction base="xs:NMTOKEN">

        <xs:enumeration value="count" />

        <xs:enumeration value="average" />

        <xs:enumeration value="sum" />

        <xs:enumeration value="stdev" />

     </xs:restriction>

  </xs:simpleType>

</xs:attribute>

But, it does not. Are you planning to support the other aggregations too, or will you update the SDK to reflect the true capabilities of the FetchXML functionality?

Monday, March 16, 2009 5:58 AM by Henk van Boeijen

# re: Support for COUNT(DISTINCT) in Fetch XML queries

The XSD is a bit out of sync with what was implemented unfortunately in CRM 4.0. However, in CRM 5, expect to see support for the following features:

1) Aggregate support for MIN, MAX, AVG, SUM, COUNT, COUNTCOLUMN

2) Group By Support in Fetch and Special Grouping Support for DateTime Fields based on Day, Month, Week and Year.

Monday, March 16, 2009 1:01 PM by Noor Merchant

Leave a Comment

(required) 
required 
(required) 

  
Enter Code Here: Required
 
Page view tracker