Fetchxml How to fetch multiple attributes in a groupby fetch (aggregate)

Question

I have a fetchxml which worked fine until I added another attribute to the fetch.

 <attribute name="ey_b_closed_conversations" alias="isClosed" /> 

this is the line I added to the fetch, and now it doesn't work. The following code is the complete fetchxml.

<fetch mapping="logical" version="1.0" aggregate="true" distinct="false"> 
  <entity name="ey_case_branch_callcenter_inquiry">
    <attribute name="ey_s_name" alias="count" aggregate="countcolumn" /> 
    <attribute name="ey_p_action" alias="id" groupby="true" /> 
    <attribute name="ey_b_closed_conversations" alias="isClosed" />  
      <filter>     
     <condition attribute="ey_case_sub_subjectid" operator="eq" value="{9E52CB8E-BEA6-E411-AFFF-0050568C0143}" />
    <condition attribute="statecode" operator="eq" value="0" />       
    </filter>     
  </entity>   
</fetch>

Are there any limitations on the number of attributes I can fetch in an aggregated fetchxml? How can I add my additional (non-aggregated) attribute to the fetch?

Answer 1

Are there any limitations on the number of attributes I can fetch in an aggregated fetchxml?

No.

How can I add my additional (non-aggregated) attribute to the fetch?

You cannot.

---

You might have received this error: An attribute can not be requested when an aggregate operation has been specified and its neither groupby nor aggregate.

This is similar to SQL error: Column 'xxxx' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This error is coming because the below line in your query does not have either groupby or aggregate property in the line.

<attribute name="ey_b_closed_conversations" alias="isClosed" />

Referring this community post:

This is because when you group by you can only include an attribute that you are not grouping-by as an aggregate ( E.g sum/min/max/count)

I tested the below and it worked:

<fetch mapping="logical" version="1.0" aggregate="true" distinct="false" >
  <entity name="account" >
    <attribute name="name" alias="count" aggregate="countcolumn" />
    <attribute name="telephone2" alias="phone" groupby="true" />
    <attribute name="telephone1" alias="mobile" aggregate="count" />
    <attribute name="accountnumber" alias="number" groupby="true" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

SQL equivalent:

SELECT   count(name) AS count,
         count(telephone1) AS mobile,
         telephone2 AS phone,
         accountnumber AS number
FROM     account
WHERE    statecode = 0
GROUP BY telephone2, accountnumber

So either do this:

<attribute name="ey_b_closed_conversations" alias="isClosed" aggregate="count" />

or

<attribute name="ey_b_closed_conversations" alias="isClosed" groupby="true" />