--- title: "Group By Functionality in Subquery Exports" slug: "group-by-functionality-in-subquery-exports" updated: 2024-04-01T01:55:16Z published: 2024-04-01T01:55:16Z --- > ## Documentation Index > Fetch the complete documentation index at: https://knowledge.technolutions.net/llms.txt > Use this file to discover all available pages before exploring further. # Group By Functionality in Subquery Exports for Advancement 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: > [!NOTE] > 1. Create a new configurable joins query on the **Person** base. > 2. Add any desired person-scoped exports, such as *Reference ID*, *First*, or *Last*. > 3. Insert a **Subquery** export and provide an easy-to-understand name, such as "Total Giving by Fund." > 4. Add a **Row Separator** (such as a comma or pipe). > 5. 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 > > [![Giving_by_Fund_Setup.PNG](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044490451.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044490451.png/Giving_by_Fund_Setup) > 6. 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. > 7. Double-click the **Gifts GUID** export, provide a new name (such as "Count of Gifts"), and modify the **Aggregate** configuration to "Count." > 8. Double-click the **Gifts Amount** export, and modify the **Aggregate** configuration to "Sum." > 9. In the Groups configuration, select the **Group** button and choose the fund name export. > > [![Giving_by_Fund_Exports.PNG](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044411692.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044411692.png/Giving_by_Fund_Exports.PNG) > 10. Add literals to format the concatenated list of exports as desired. > > [![Giving_by_Fund_Formatted.PNG](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044491091.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044491091.png/Giving_by_Fund_Formatted.PNG) > 11. 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: [![Giving_by_Fund_Final.PNG](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044491131.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044491131.png/Giving_by_Fund_Final.PNG) ## Group Giving by Year To display the count and sum of gifts given by a person by year: > [!NOTE] > 1. Create a new configurable joins query on the **Person** base. > 2. Add any desired person-scoped exports, such as Reference ID, First, or Last. > 3. Insert a **Subquery** export and provide an easy-to-understand name, such as "Total Giving by Year." > 4. Add a **Row Separator** (such as a comma or pipe). > 5. Insert a **Join** to **Gifts** (this join connects the person with all of their gifts). > > [![Giving_by_Year_Setup.PNG](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044491511.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044491511.png/Giving_by_Year_Setup.PNG) > 6. 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. > 7. 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**. > 8. Click **Save**. > 9. Double-click the **Gifts GUID** export, provide a new name (such as "Count of Gifts"), and modify the **Aggregate** configuration to "Count." > 10. Double-click the **Gifts Amount** export, and modify the **Aggregate** configuration to "Sum." > 11. 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**. > > [![Giving_by_Year_Exports.PNG](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044491791.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044491791.png/Giving_by_Year_Exports.PNG) > 12. Add literals, as necessary, to format the concatenated list of exports as desired. > > [![Giving_by_Year_Formatted.PNG](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044491831.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044491831.png/Giving_by_Year_Formatted.PNG) > 13. 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: [![Giving_by_Year_Output.PNG](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044412672.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/360044412672.png/Giving_by_Year_Output.PNG)