Distinct values in a subquery export can be grouped together using the Groups function and can be helpful in displaying the sums or counts associated with a distinct value, such as how many times a person has given to a particular fund or how much a person has given in any particular year.
This article provides examples of how these types of calculations are used in the query tool.
Group Giving by Fund
To display the count and sum of gifts given by a person to each fund:
- Create a new configurable joins query on the Person base.
- Add any desired person-scoped exports, such as Reference ID, First, or Last.
- Insert a Subquery export and provide an easy-to-understand name, such as "Total Giving by Fund."
- Add a Row Separator (such as a comma or pipe).
- Insert two Joins:
- Gift: Connects the person with all of their gifts
- Fund: Joined off of the Gift base and connects each gift with its associated fund
- Add the following exports:
- Fund Name: Since this is what the query will ultimately be grouped by, it must be included as one of the exports.
- Gift GUID: This export will be used to count the distinct number of gifts.
- Gift Amount: This export will enable the sum of all gifts to the fund to be aggregated.
- Double-click the Gifts GUID export, provide a new name (such as "Count of Gifts"), and modify the Aggregate configuration to "Count."
- Double-click the Gifts Amount export, and modify the Aggregate configuration to "Sum."
- In the Groups configuration, select the Group button and choose the fund name export.
- Add literals to format the concatenated list of exports as desired.
- Click Save.
The final query output should resemble the following pipe-separated list of the funds to which a person has given, as well as the count of gifts and the total amount to that particular fund:
Group Giving by Year
To display the count and sum of gifts given by a person by year:
- Create a new configurable joins query on the Person base.
- Add any desired person-scoped exports, such as Reference ID, First, or Last.
- Insert a Subquery export and provide an easy-to-understand name, such as "Total Giving by Year."
- Add a Row Separator (such as a comma or pipe).
- Insert a Join to Gifts (this join connects the person with all of their gifts).
- Add the following exports:
- Gift Date: Since this is what the query will ultimately be grouped by, it must be included as one of the exports.
- Gift GUID: This export will be used to count the number of distinct gifts.
- Gift Amount: This export will be used to aggregate and display the sum of all gifts in a given year.
- Since the gift's year is the distinct value that a person's giving will ultimately be grouped by, the date must be formatted to only return the year of the gift. Double-click the Gifts Date export, change the Format Type to Date, and add "yyyy" as the Format Mask.
- Click Save.
- Double-click the Gifts GUID export, provide a new name (such as "Count of Gifts"), and modify the Aggregate configuration to "Count."
- Double-click the Gifts Amount export, and modify the Aggregate configuration to "Sum."
- In the Groups configuration, select the Group button and choose the gift date export. This export must also be formatted to return only the year of the gift. Double-click the export, change the Format Type to Date, and add "yyyy" as the Format Mask.
- Add literals, as necessary, to format the concatenated list of exports as desired.
- Click Save.
The final query output should resemble the following pipe-separated list of years a person has given, as well as the count of gifts and the total amount given each year:
