Answers.Body: <p>I think the following will provide you the average on sumfield extension</p>
  <pre><code>(SELECT COALESCE(SUM(total_amount), 0) / NULLIF(COUNT(*), 0)
  FROM civicrm_contribution t1 
  WHERE t1.contact_id = NEW.contact_id 
  AND t1.financial_type_id IN (%financial_type_ids) 
  AND t1.contribution_status_id = 1 AND t1.is_test = 0)
  </code></pre>
  <p>Pete's answer to use SK is the more recommended approach.</p>
  <p>The FormBuilder extension allows you to attach the Search you just created using searchkit =&gt; display it on the contact summary page (as a new block or tab).</p>
  <p>It will only show the average of contribution specific to the viewed contact cid</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-17T06:15:55.530Z
Answers.LastActivityDate: 2024-05-17T06:15:55.530Z
Answers.UserDisplayName: jitendra - fuzion
Answers.Body: <p>I am adding this as an Answer which does not answer your request foa Summary Field solution, but it was v quick to put together via SK in case it is of help to others.</p>
  <ul>
  <li>SK based on Contact</li>
  <li>'with required entity' Contribution</li>
  <li>'group by' contact id</li>
  <li>then under 'select fields' set Amount to be Averaged</li>
  <li>then probably 'add' another field and set Amount to be 'count'
  <a href="https://i.sstatic.net/Lh4UQaGd.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/Lh4UQaGd.png" alt="enter image description here" /></a></li>
  </ul>
  <p>so you get something like</p>
  <p><a href="https://i.sstatic.net/ObVwNP18.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/ObVwNP18.png" alt="enter image description here" /></a></p>
  <p>which gives you this</p>
  <p><a href="https://i.sstatic.net/E4DOEXOZ.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/E4DOEXOZ.png" alt="enter image description here" /></a></p>
  <p>and here is an export</p>
  <p>[
  [
  &quot;SavedSearch&quot;,
  &quot;save&quot;,
  {
  &quot;records&quot;: [
  {
  &quot;name&quot;: &quot;Contact_Search_by_demo_example_com&quot;,
  &quot;label&quot;: &quot;Contact Search by demo@example.com&quot;,
  &quot;api_entity&quot;: &quot;Contact&quot;,
  &quot;api_params&quot;: {
  &quot;version&quot;: 4,
  &quot;select&quot;: [
  &quot;id&quot;,
  &quot;sort_name&quot;,
  &quot;contact_type:label&quot;,
  &quot;contact_sub_type:label&quot;,
  &quot;AVG(Contact_Contribution_contact_id_01.total_amount) AS AVG_Contact_Contribution_contact_id_01_total_amount&quot;,
  &quot;COUNT(Contact_Contribution_contact_id_01.total_amount) AS COUNT_Contact_Contribution_contact_id_01_total_amount&quot;
  ],
  &quot;orderBy&quot;: [],
  &quot;where&quot;: [],
  &quot;groupBy&quot;: [
  &quot;id&quot;
  ],
  &quot;join&quot;: [
  [
  &quot;Contribution AS Contact_Contribution_contact_id_01&quot;,
  &quot;INNER&quot;,
  [
  &quot;id&quot;,
  &quot;=&quot;,
  &quot;Contact_Contribution_contact_id_01.contact_id&quot;
  ]
  ]
  ],
  &quot;having&quot;: []
  }
  }
  ],
  &quot;match&quot;: [
  &quot;name&quot;
  ]
  }
  ]
  ]</p>
  <p>EDITED</p>
  <p>And since SK also shows the sql involved, here is the query</p>
  <p>SELECT <code>a</code>.<code>id</code> AS <code>id</code>, <code>a</code>.<code>sort_name</code> AS <code>sort_name</code>, <code>a</code>.<code>contact_type</code> AS <code>contact_type:label</code>, <code>a</code>.<code>contact_sub_type</code> AS <code>contact_sub_type:label</code>, AVG(<code>Contact_Contribution_contact_id_01</code>.<code>total_amount</code>) AS <code>AVG_Contact_Contribution_contact_id_01_total_amount</code>, COUNT(<code>Contact_Contribution_contact_id_01</code>.<code>total_amount</code>) AS <code>COUNT_Contact_Contribution_contact_id_01_total_amount</code>
  FROM civicrm_contact a
  INNER JOIN (<code>civicrm_contribution</code> <code>Contact_Contribution_contact_id_01</code>) ON <code>a</code>.<code>id</code> = <code>Contact_Contribution_contact_id_01</code>.<code>contact_id</code>
  WHERE (<code>a</code>.<code>is_deleted</code> = &quot;0&quot;)
  GROUP BY <code>a</code>.<code>id</code>
  ORDER BY <code>a</code>.<code>sort_name</code> ASC
  LIMIT 50
  OFFSET 0</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-10T00:10:29.400Z
Answers.Text: Thank you for your search kit solution - I would still like summary fields help as I need to be able to see the average on an individual contact record - not as a report
Answers.UserDisplayName: Greta
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-10T00:18:11.713Z
Answers.Text: Might you be able to change your answer to a comment as otherwise people will think it is answered?
Answers.UserDisplayName: Greta
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-10T19:45:14.407Z
Answers.Text: Can't do a comment with screenshots etc - and provided you don't 'accept' the answer then it shouldn't cause others to not add an answer
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-10T19:48:27.230Z
Answers.Text: if there isn't a Summary Fields solution for you by Monday i will get a colleague to add one for you
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-10T19:51:04.893Z
Answers.Text: oh - and a SK will show you the SQL - will add to the Answer in case it helps you. If you prefer I will remove this Answer all together.
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-11T01:31:27.540Z
Answers.Text: Peted thank you so much! I have set up the SK search as it is super useful. Lets leave all this up as I'm sure other users will benefit.
Answers.UserDisplayName: Greta
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-16T01:53:31.810Z
Answers.Text: Hello Peted did you manage to find a colleague to add the solution? Many thanks Greta
Answers.UserDisplayName: Greta
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-09T05:18:14.867Z
Answers.LastActivityDate: 2024-05-10T19:52:13.107Z
Answers.LastEditDate: 2024-05-10T19:52:13.107Z
Answers.UserDisplayName: petednz - fuzion
Body: <p>I'd like to add a field for average contribution in the &quot;summary fields&quot; extension - I know I can divide total contribs by count but the staff at my org can't manage this. I also know that the average is displayed on the contacts contribution page, However this is rendered invalid by our use of contributions for non-financial transactions.
  The instructions are <a href="https://github.com/progressivetech/net.ourpowerbase.sumfields/blob/master/DEVELOPERS.md#create-a-new-summary-field" rel="nofollow noreferrer">here</a> but my sql is woeful.
  Any assistance much appreciated</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-05-09T05:10:51.810Z
Comments.Text: The other option these days is to use SearchKit for your report building as afaik it allows for some calculations
Comments.UserDisplayName: petednz - fuzion
Tags: <summaryfields><custom>
Title: help with creating new summary field
ThreadText: # help with creating new summary field
  
  (Asked by: username_0 on 2024-05-09)
  
  I'd like to add a field for average contribution in the "summary fields" extension - I know I can divide total contribs by count but the staff at my org can't manage this. I also know that the average is displayed on the contacts contribution page, However this is rendered invalid by our use of contributions for non-financial transactions.
  The instructions are [here](https://github.com/progressivetech/net.ourpowerbase.sumfields/blob/master/DEVELOPERS.md#create-a-new-summary-field) but my sql is woeful.
  Any assistance much appreciated
  
  **Tags:** summaryfields, custom
  
  > **Comments:**
  > **username_1**: The other option these days is to use SearchKit for your report building as afaik it allows for some calculations  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2024-05-17. Score: 3
  
  I think the following will provide you the average on sumfield extension
  
  ```
  (SELECT COALESCE(SUM(total_amount), 0) / NULLIF(COUNT(*), 0)
  FROM civicrm_contribution t1 
  WHERE t1.contact_id = NEW.contact_id 
  AND t1.financial_type_id IN (%financial_type_ids) 
  AND t1.contribution_status_id = 1 AND t1.is_test = 0)
  
  ```
  
  Pete's answer to use SK is the more recommended approach.
  
  The FormBuilder extension allows you to attach the Search you just created using searchkit => display it on the contact summary page (as a new block or tab).
  
  It will only show the average of contribution specific to the viewed contact cid
  
  
  ---
  
  ### Answer by username_1 on 2024-05-09. Score: 2
  
  I am adding this as an Answer which does not answer your request foa Summary Field solution, but it was v quick to put together via SK in case it is of help to others.
  
  * SK based on Contact
  * 'with required entity' Contribution
  * 'group by' contact id
  * then under 'select fields' set Amount to be Averaged
  * then probably 'add' another field and set Amount to be 'count'
    [![enter image description here](https://i.sstatic.net/Lh4UQaGd.png)](https://i.sstatic.net/Lh4UQaGd.png)
  
  so you get something like
  
  [![enter image description here](https://i.sstatic.net/ObVwNP18.png)](https://i.sstatic.net/ObVwNP18.png)
  
  which gives you this
  
  [![enter image description here](https://i.sstatic.net/E4DOEXOZ.png)](https://i.sstatic.net/E4DOEXOZ.png)
  
  and here is an export
  
  [
  [
  "SavedSearch",
  "save",
  {
  "records": [
  {
  "name": "Contact\_Search\_by\_demo\_example\_com",
  "label": "Contact Search by demo@example.com",
  "api\_entity": "Contact",
  "api\_params": {
  "version": 4,
  "select": [
  "id",
  "sort\_name",
  "contact\_type:label",
  "contact\_sub\_type:label",
  "AVG(Contact\_Contribution\_contact\_id\_01.total\_amount) AS AVG\_Contact\_Contribution\_contact\_id\_01\_total\_amount",
  "COUNT(Contact\_Contribution\_contact\_id\_01.total\_amount) AS COUNT\_Contact\_Contribution\_contact\_id\_01\_total\_amount"
  ],
  "orderBy": [],
  "where": [],
  "groupBy": [
  "id"
  ],
  "join": [
  [
  "Contribution AS Contact\_Contribution\_contact\_id\_01",
  "INNER",
  [
  "id",
  "=",
  "Contact\_Contribution\_contact\_id\_01.contact\_id"
  ]
  ]
  ],
  "having": []
  }
  }
  ],
  "match": [
  "name"
  ]
  }
  ]
  ]
  
  EDITED
  
  And since SK also shows the sql involved, here is the query
  
  SELECT `a`.`id` AS `id`, `a`.`sort_name` AS `sort_name`, `a`.`contact_type` AS `contact_type:label`, `a`.`contact_sub_type` AS `contact_sub_type:label`, AVG(`Contact_Contribution_contact_id_01`.`total_amount`) AS `AVG_Contact_Contribution_contact_id_01_total_amount`, COUNT(`Contact_Contribution_contact_id_01`.`total_amount`) AS `COUNT_Contact_Contribution_contact_id_01_total_amount`
  FROM civicrm\_contact a
  INNER JOIN (`civicrm_contribution` `Contact_Contribution_contact_id_01`) ON `a`.`id` = `Contact_Contribution_contact_id_01`.`contact_id`
  WHERE (`a`.`is_deleted` = "0")
  GROUP BY `a`.`id`
  ORDER BY `a`.`sort_name` ASC
  LIMIT 50
  OFFSET 0
  
  > **Comments:**
  > **username_0**: Thank you for your search kit solution - I would still like summary fields help as I need to be able to see the average on an individual contact record - not as a report  
  > **username_0**: Might you be able to change your answer to a comment as otherwise people will think it is answered?  
  > **username_1**: Can't do a comment with screenshots etc - and provided you don't 'accept' the answer then it shouldn't cause others to not add an answer  
  > **username_1**: if there isn't a Summary Fields solution for you by Monday i will get a colleague to add one for you  
  > **username_1**: oh - and a SK will show you the SQL - will add to the Answer in case it helps you. If you prefer I will remove this Answer all together.  
  > **username_0**: Peted thank you so much! I have set up the SK search as it is super useful. Lets leave all this up as I'm sure other users will benefit.  
  > **username_0**: Hello Peted did you manage to find a colleague to add the solution? Many thanks username_0  
  
  ---
  
  