Using Count Aggregation in FetchXML
[Applies to: Microsoft Dynamics CRM 4.0]
Find the latest SDK documentation: CRM 2015 SDK
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\&.
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:
|<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|
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>
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>