Home > CRM, Javista, Microsoft, Microsoft Dynamics, Microsoft Dynamics CRM Online, Resource Center > Support for COUNT(DISTINCT) in Fetch XML queries

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

Advertisements
  1. April 15, 2013 at 12:29 am

    Biggest Loser trainer, Bob Harper, offers a great
    explanation of the lack of proper blood flow to the affected tissues.
    Diet- What is it? If you lean toward vegetarianism, neither diet
    is likely to appeal to you.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: