Distinct values within a subquery export may be grouped together using the Groups functionality. Often, this type of functionality is helpful in displaying 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.
Click the headings below for examples of how to do these types of calculations within the query tool.
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 name.
- 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- This join connects the person with all of their gifts
- Fund - Joined off of the Gift base, this connects each gift with its associated fund.
- Add the following exports:
- Fund Name - Since this is what the query is ultimately going to 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 be aggregated to display the sum of all gifts to the fund.
- 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, modify the Aggregate configuration to "Sum."
- In the Groups configuration, select the Group button and choose the fund name export.
- 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 the funds to which a person has given, as well as the count of gifts and the total amount to that particular fund:
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 name.
- 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 is ultimately going to 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 be aggregated to 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, 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:
