Answers.Body: <p>It's grouping by civicrm_contact_civicrm_participant__civicrm_value_contact_c_1 which is the custom field row id, but it's not being group_concat'd in the select. So it's going to have a row for each different contact. Or I'm not sure why it's even being included in the query.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-07-02T13:26:06.430Z
Answers.LastActivityDate: 2023-07-02T13:26:06.430Z
Answers.UserDisplayName: Demerit
Body: <p>Using CiviCRM Entity
  commit 5295af29e5bac4575f991683b1c061b96a0e0713 (HEAD -&gt; 8.x-3.x, origin/HEAD, origin/8.x-3.x)</p>
  <p>Trying to get this to aggregate properly -&gt;
  <a href="https://i.sstatic.net/qqfLk.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/qqfLk.png" alt="enter image description here" /></a></p>
  <p>But this should only show 3 rows with the final row:</p>
  <p>Other 50-60 yrs 2</p>
  <p>Gender is the CiviCRM Core field. Age Range is a CiviCRM Custom (select) field.</p>
  <p>When compared to D7 -&gt; we are noting <strong>2 MIN()</strong> getting injected into the query:</p>
  <pre><code>SELECT &quot;civicrm_contact_civicrm_participant&quot;.&quot;gender_id&quot; AS &quot;civicrm_contact_civicrm_participant_gender_id&quot;, &quot;civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3&quot;.&quot;age_range_61&quot; AS &quot;civicrm_contact_civicrm_participant__civicrm_value_contact_c&quot;, &quot;civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3&quot;.&quot;id&quot; AS &quot;civicrm_contact_civicrm_participant__civicrm_value_contact_c_1&quot;, COUNT(DISTINCT civicrm_participant.contact_id) AS &quot;civicrm_participant_contact_id&quot;, MIN(civicrm_participant.id) AS &quot;id&quot;, MIN(civicrm_contact_civicrm_participant.id) AS &quot;civicrm_contact_civicrm_participant_id&quot;
  FROM
  {civicrm_participant} &quot;civicrm_participant&quot;
  LEFT JOIN drupal_karin.civicrm_contact &quot;civicrm_contact_civicrm_participant&quot; ON civicrm_participant.contact_id = civicrm_contact_civicrm_participant.id
  LEFT JOIN drupal_karin.civicrm_value_contact_custo_3 &quot;civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3&quot; ON civicrm_contact_civicrm_participant.id = civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3.entity_id
  WHERE &quot;civicrm_participant&quot;.&quot;event_id&quot; = '32'
  GROUP BY civicrm_contact_civicrm_participant_gender_id, civicrm_contact_civicrm_participant__civicrm_value_contact_c, civicrm_contact_civicrm_participant__civicrm_value_contact_c_1
  LIMIT 11 OFFSET 0
  </code></pre>
  <p>UPDATE:
  If I remove the custom field Age Range from the equation (and only leave the native CiviCRM Core Gender field) -&gt; then the View produces the proper count:</p>
  <p><a href="https://i.sstatic.net/RtQtL.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/RtQtL.png" alt="enter image description here" /></a></p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-08-17T18:55:22.457Z
Comments.Text: I think the problem is due to the relationship to the custom field:
  
  ```
  "civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3"."id" AS "civicrm_contact_civicrm_participant__civicrm_value_contact_c_1",
  ```
  
  From
  
  ```
  LEFT JOIN drupal_karin.civicrm_value_contact_custo_3 "civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3" ON civicrm_contact_civicrm_participant.id = civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3.entity_id
  ```
  
  It's being added to `GROUP BY` as `civicrm_contact_civicrm_participant__civicrm_value_contact_c_1`.
Comments.UserDisplayName: Matt Glaman
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-08-17T18:57:29.083Z
Comments.Text: I think the key is the missing MIN(civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3.entity_id)
Comments.UserDisplayName: Matt Glaman
Tags: <drupal-views><civicrm-entity>
Title: Getting aggregation to work in D9/D10 Views with CiviCRM relationships
ThreadText: # Getting aggregation to work in D9/D10 Views with CiviCRM relationships
  
  (Asked by: username_0 on 2023-07-02)
  
  Using CiviCRM Entity
  commit 5295af29e5bac4575f991683b1c061b96a0e0713 (HEAD -> 8.x-3.x, origin/HEAD, origin/8.x-3.x)
  Trying to get this to aggregate properly ->
  
  But this should only show 3 rows with the final row:
  Other 50-60 yrs 2
  Gender is the CiviCRM Core field. Age Range is a CiviCRM Custom (select) field.
  When compared to D7 -> we are noting 2 MIN() getting injected into the query:
  SELECT "civicrm_contact_civicrm_participant"."gender_id" AS "civicrm_contact_civicrm_participant_gender_id", "civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3"."age_range_61" AS "civicrm_contact_civicrm_participant__civicrm_value_contact_c", "civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3"."id" AS "civicrm_contact_civicrm_participant__civicrm_value_contact_c_1", COUNT(DISTINCT civicrm_participant.contact_id) AS "civicrm_participant_contact_id", MIN(civicrm_participant.id) AS "id", MIN(civicrm_contact_civicrm_participant.id) AS "civicrm_contact_civicrm_participant_id"
  FROM
  {civicrm_participant} "civicrm_participant"
  LEFT JOIN drupal_karin.civicrm_contact "civicrm_contact_civicrm_participant" ON civicrm_participant.contact_id = civicrm_contact_civicrm_participant.id
  LEFT JOIN drupal_karin.civicrm_value_contact_custo_3 "civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3" ON civicrm_contact_civicrm_participant.id = civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3.entity_id
  WHERE "civicrm_participant"."event_id" = '32'
  GROUP BY civicrm_contact_civicrm_participant_gender_id, civicrm_contact_civicrm_participant__civicrm_value_contact_c, civicrm_contact_civicrm_participant__civicrm_value_contact_c_1
  LIMIT 11 OFFSET 0
  
  UPDATE:
  If I remove the custom field Age Range from the equation (and only leave the native CiviCRM Core Gender field) -> then the View produces the proper count:
  
  **Tags:** drupal-views, civicrm-entity
  
  > **Comments:**
  > **username_1**: I think the problem is due to the relationship to the custom field:  ``` "civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3"."id" AS "civicrm_contact_civicrm_participant__civicrm_value_contact_c_1", ```  From  ``` LEFT JOIN drupal_karin.civicrm_value_contact_custo_3 "civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3" ON civicrm_contact_civicrm_participant.id = civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3.entity_id ```  It's being added to `GROUP BY` as `civicrm_contact_civicrm_participant__civicrm_value_contact_c_1`.  
  > **username_1**: I think the key is the missing MIN(civicrm_contact_civicrm_participant__civicrm_value_contact_custo_3.entity_id)  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2023-07-02. Score: 1
  
  It's grouping by civicrm\_contact\_civicrm\_participant\_\_civicrm\_value\_contact\_c\_1 which is the custom field row id, but it's not being group\_concat'd in the select. So it's going to have a row for each different contact. Or I'm not sure why it's even being included in the query.
  
  
  ---
  
  