{"id":105,"date":"2011-04-23T08:41:00","date_gmt":"2011-04-23T03:11:00","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=105"},"modified":"2021-08-25T11:17:06","modified_gmt":"2021-08-25T11:17:06","slug":"group-by-using-fetchxml","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2011\/04\/group-by-using-fetchxml\/","title":{"rendered":"Group by using FetchXML"},"content":{"rendered":"

In Microsoft Dynamics CRM 2011\u00a0FetchXML<\/strong>\u00a0includes grouping and aggregation features which let us to use aggregate functions like sum, count etc.
\nUsing Group by we can calculate sum, avg, min, max, count. But, Group by clause is not supported through LINQ in CRM.
\nYou can only specify one aggregate attribute in a query and you cannot use the distinct keyword. To create an aggregate attribute, set the keyword aggregate to true, then specify valid entity name, attribute name and alias(variable name). You must also specify the type of aggregation you want to perform.
\n<fetch distinct=’false’ mapping=’logical’ aggregate=’true’>
\n<entity name=’entity name’>
\n<attribute name=’attribute name’ aggregate=’count’ alias=’alias name’\/>
\n<\/entity>
\n<\/fetch>”
\nBelow is the example to get sum of total amount of all won quotes:<\/strong>
\nstring quotes = @”
\n<fetch distinct=’false’ mapping=’logical’ aggregate=’true’>
\n<entity name=’quote’>
\n<attribute name=’totalamount’ alias=’totalamount_sum’ aggregate=’sum’\/>
\n<attribute name=’statecode’ groupby=’true’ alias=’state’ \/>
\n<filter type=’and’>
\n<condition attribute=’ statecode ‘ operator=’eq’ value=’won’ \/>”+
\n“<\/filter> “+
\n“<\/entity> “+
\n“<\/fetch>”;
\nEntityCollection quotes_result = _service.RetrieveMultiple(new FetchExpression(quotes));
\nforeach (var q in quotes_result.Entities)
\n{
\nDecimal wonQuoteAmount = ((Money)((AliasedValue)q[“totalamount_sum”]).Value).Value;
\n}<\/p>\n

Free 70% of storage space in CRM with Attachment Management Apps!<\/div><\/div><\/h2>\n

Attach2Dynamics<\/a> – Store and manage documents\/attachments in cloud storage of your choice – SharePoint, Dropbox or Azure Blob Storage from within Dynamics 365 CRM.<\/em>
\nSharePoint Security Sync<\/a><\/strong> \u2013 Robust and secure solution to integrate Dynamics 365 CRM and SharePoint Security Sync thereby ensuring secure access to confidential documents stored in SharePoint.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

In Microsoft Dynamics CRM 2011\u00a0FetchXML\u00a0includes grouping and aggregation features which let us to use aggregate functions like sum, count etc. Using Group by we can calculate sum, avg, min, max, count. But, Group by clause is not supported through LINQ in CRM. You can only specify one aggregate attribute in a query and you cannot\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[19],"tags":[123,795,797],"class_list":["post-105","post","type-post","status-publish","format-standard","hentry","category-dynamics-crm","tag-aggregation-feature","tag-fetchxml","tag-fetchxml-dynamics-crm"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/105","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=105"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/105\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=105"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=105"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=105"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}