Export (0) Print
Expand All

Using Count Aggregation in FetchXML

banner art

[Applies to: Microsoft Dynamics CRM 4.0]

This feature requires Microsoft Dynamics CRM 4.0 Update Rollup 2, that can be found at www.microsoft.com/downloads/details.aspx?familyid=aa671769-61e9-45c4-919f-c88199aa4241&displaylang=en&tm.

To this point we only supported the count aggregate function which performed a count(*) on the base table of the entity, returning the number of records found.

With the release of Update Rollup 2, we have improved this functionality adding a new aggregate operation, countcolumn, which performs the SQL count operation for a specified column. It also supports the distinct keyword which only counts unique columns. If the distinct keyword is not specified with a countcolumn aggregate, its value has a default value of false.

The following table summarizes the Fetch aggregate types and the equivalent SQL translation:

FetchXMLSQL
<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

Example

This sample FetchXML statement shows a query to return a count of all accounts which have a county specified in their address.

<fetch aggregate='true'>
     <entity name='account'>
     <attribute name='address1_county' aggregate='countcolumn' alias='countyAccounts' />
     </entity>
</fetch>

Example

This sample FetchXML statement shows a query to return a count of states in which we have an account. This uses the distinct keyword which counts a state only one time.

<fetch aggregate='true'>
     <entity name='account'>
     <attribute name='address1_stateorprovince' aggregate='countcolumn' alias='countStates' distinct='true' />
     </entity>
</fetch>

See Also

Concepts

Reference

Other Resources


© 2010 Microsoft Corporation. All rights reserved.


Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft