0

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 but my sql is woeful. Any assistance much appreciated

1
  • The other option these days is to use SearchKit for your report building as afaik it allows for some calculations
    – petednz - fuzion
    Commented May 9 at 5:10

2 Answers 2

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

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

so you get something like

enter image description here

which gives you this

enter image description here

and here is an export

[ [ "SavedSearch", "save", { "records": [ { "name": "Contact_Search_by_demo_example_com", "label": "Contact Search by [email protected]", "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

7
  • 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
    – Greta
    Commented May 10 at 0:10
  • Might you be able to change your answer to a comment as otherwise people will think it is answered?
    – Greta
    Commented May 10 at 0:18
  • 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
    – petednz - fuzion
    Commented May 10 at 19:45
  • if there isn't a Summary Fields solution for you by Monday i will get a colleague to add one for you
    – petednz - fuzion
    Commented May 10 at 19:48
  • 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.
    – petednz - fuzion
    Commented May 10 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.