How to query donors based on their total giving
This article shows you how to create a list of donors based on the total amount of their gifts. The provided example demonstrates how to generate total giving data. After you understand the basic method, you can use a variety of filters to get the exact information you desire.
Sections in this article
How to query total giving data
Example of a query on total giving
How to query total giving data
The Total Contributions column enables you to view total giving data.
It is important to understand that even though the Total Contributions column is listed in the Contributions group in the Column Picker panel, you cannot select the column directly as a Result column. When designing a query, you must select this column in the Query Conditions panel and apply it as a condition on another column whose values are aggregated by the SUM function.
Depending on the exact nature of your question, you can refine your results by applying additional filter criteria, such as a specific fund name, fund type, and a custom date range, to name a few.
Example of a query on total giving
This basic example shows how to query donors based on the total amount of gifts. Examine it closely to understand the basic concept. The donor list you generate in the results set is determined by the conditions you apply (in the Query Conditions panel). You have the option to apply a variety of conditions to get the exact information you need to answer your question.
Scenario
Suppose you want to obtain a list of families whose total giving to all funds was $250 or more.
Question
Our question is as follows:
What are the names of families who contributed more than $250 to all funds?
Setup
-
Select the information you need. In the Column Picker panel, select these columns:
-
Families > Last Name
-
Funds > Fund ID
-
Contributions > Payment Amount
-
-
Because you want the sum total of the donor's payments, apply the aggregate SUM function to the Payment Amount column. To do this, in the Result Columns panel, click
to the right Contributions Payment and select SUM from the menu (see next illustration).
Caution
To obtain the correct results, you must apply the SUM aggregate function to the Contributions Payment Amount column. When the query runs, the SUM aggregate function calculates the sum of the values in the Payment Amount column to arrive at each family’s total gift amount.
-
You want to view only those donors whose total contributions are $250 or more. To do this, in the Query Conditions panel, select the Contributions > Total Contributions column and apply this filter: is greater than or equal to 250.
You query setup now looks like this:
Run the Query
To run the query, click Execute Query.
The results set contains the names of nine families whose total contribution to all funds is greater than or equal to $250. The Contributions Payment Amount Sum column in the Query Results panel shows each family's total contribution amount.
Additional Considerations
To further refine the list of donors in the results set, simply include more or different filter conditions. For example:
- To view total contributions for a specific fund, in the Query Conditions panel, apply one of fund identifier conditions, such as Funds > Fund ID is equal to or Funds Description starts with [enter value].
- To view total contribution amounts to tax-deductible funds only, in the Query Conditions panel, apply this condition: Funds > is Tax Deductible.
- To view total contributions during a given date range, in the Query Conditions panel, apply this condition: Contributions > Payment Date is between 00:00 and 00:00.
Related Articles
How to reorder the columns in your query results
How to find all results that do not match a condition
How to rename a column heading in your query results
Updated