It’s time for another fun query tip! This time I want to shine the spotlight on an often overlooked feature in query - the “summarize query output” functionality.
The summarize query output feature can be found on the first tab of the ad-hoc query window, under Result fields to display. You can apply one of the summarize functions to an output field you have selected in your query. This enables you to view a calculation of the field results rather than the actual contents of the field.
You can see the summarize button above (it’s a blue Sigma symbol). When you click the button, a drop-down appears with the following summary function scenarios:
COUNT: For number or amount fields, the COUNT function is the total number of records included.
SUM: For number or amount fields, the SUM function is the combined total of the numbers.
AVG: For number or amount fields, the AVG function is the average of the numbers included.
MAX: For date fields, the MAX function displays the maximum or latest date. For number or amount fields, MAX displays the largest number.
MIN: For date fields, the MIN function displays the minimum or oldest date. For number or amount fields, MIN displays the lowest number.
As you can see, the way the summarize feature operates depends on the summarize function we select and the field we use it with. The results of the summarize button are also impacted by the other fields we have selected in our output.
Let’s look at an example of this feature in action. Let’s say I want to analyze giving from my board members. I could build a query like the one below to see their giving activity from the last calendar year:
Tip: Make sure you include filters to narrow down the gift types that you’d like included in your results. In this example I’m limiting it to transaction type = payment so that I don’t double count pledges and pledge payments, which would inflate the totals I’m about to calculate. You should also make sure you are using the Application Details > Amount field in your output to correctly display split gifts.
This is a good start, but it’s a little hard to wade through all of these transactions and see what is really going on. To make things a little cleaner, I could add the SUM function to the amount field in my output.
When I run the query again, my results look like this:
Now I can see each board member, along with a summary of the amount they gave to each designation or fund during the last calendar year. To streamline, even more, I can remove the designation field from my output:
With this small change, I can see total giving across all designations during the last calendar year for each board member.
To demonstrate how a different summary function could be applied to this example, let’s change the function applied to the amount field from SUM to MAX.
Now our query results show the largest gift that each board member made during the last calendar year:
This is just one quick example of how you can utilize the summary functions in the ad-hoc query tool of Blackbaud CRM™ to your advantage. The possibilities are endless! Hopefully this tutorial gives you the knowledge and confidence to try it out.