Summarize Query Output

When you add or edit an ad-hoc query and select a field under Result fields to display on the Select filter and output fields tab, the Summarize button enables you to view a calculation of the field results rather than the actual contents of the field.

Query is a way to group records; it is not a reporting tool. As such, the output fields identify records included in the query. If a field with a one-to-many relationship is selected under Include records where or Result fields to display, the record may appear multiple times in the query: once for each time the record satisfies the selected criteria. This allows you to check your query to ensure you get the expected results.

For example, you may query to find everyone who has donated gifts to your organization this year.

  • Filter criteria: Revenue\Application Details\Type equals “Gift” AND Revenue\Application Details\Revenue Details\Date equals “This calendar year”

  • Output Fields: Name AND Revenue\Application Details\Amount

If you include these fields, the output displays a unique row in the query for each gift. For your purposes, these may be considered duplicates. For instance, if a donor made five gifts, his name appears five times with the amount of each gift listed separately.

The Summarize button's functions - Count, Sum, Average, Min, Max - can help prevent these duplicates.

If you use the SUM function for the amount, the donor appears once in the query with his gift revenue summarized. If you add a second amount field for the output and use the COUNT function for it, the donor appears once in the query and the second amount COUNT field would be “5,” the total number of gifts from the donor this year.

Some summary function scenarios include:

  • 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.