Aggregate Count of linked entity in fetch XML won't return 0

Question

Problem

I have a Fetch XML query that has an aggregate count on a linked entity that isn't behaving as expected. Basically I'm returning a list of courses, and getting a count of how many contacts are currently registered for the course, but even if no one is registered for the course, I'm getting a count of 1 contacts registered. If I register 1 contact, I get a count of 1. If I register 5 contacts, I get a count of 5, so the issue seems to be resolving around not being able to get a count of 0, when there are no related records.

Here is the Fetch XML

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
  <entity name="new_coursesection">
    <attribute name="new_termscheduleid" groupby="true" alias="new_termscheduleid" />
    <attribute name="new_termid" groupby="true" alias="new_termid" />
    <attribute name="new_sectionname" groupby="true" alias="new_sectionname" />
    <attribute name="new_name" groupby="true" alias="new_name" />
    <filter type="and">
      <condition attribute="new_courseid" operator="eq" value="{some guid}" />
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <link-entity name="new_contactcoursesection" from="new_coursesectionid" to="new_coursesectionid" alias="new_contactcoursesection1" link-type="outer">
      <attribute name="new_contactcoursesectionid" aggregate="count" alias="coursecount" />
      <filter>
        <condition attribute="statecode" operator="eq" value="0"/>
      </filter>
      <order alias="coursecount" descending="true" />
    </link-entity>
  </entity>
</fetch>

Has anyone encountered this issue and know a work around?

Coagulum

Answer 1

Figured out the issue after looking at the generated SQL with the trace log enabled. Had to change count to countcolumn:

Broken

<attribute name="new_contactcoursesectionid" aggregate="count" alias="coursecount" />

Fix

<attribute name="new_contactcoursesectionid" aggregate="countcolumn" alias="coursecount" />

Although I believe there is a bug in CRM 2011 when dealing with aggregate values of a linked entity in FetchXML. Even though it is marked with an alias, the AliasedValue in the Attribute collection will use the actual CRM name, rather than the aliased one, even though the AttributeCollection's key will correctly use the aliased name. Weird...

Coagulum

bugger, this returns Null in the count too, when I left join. not so good. – Meet

there is still a bug in CRM Online 2016, if you put two CountColumns in one fetch statement it doesn't give you the correct count for each of the aliases. and you can not display any countcolumns in a View. Does MS not test this stuff before releasing it? – Meet