Advanced Query Wizard: Aggregate Query Results

Aggregation allows you to determine the Minimum, Maximum, Average, Sum, Count, or Distinct Count of any numeric field. 

Aggregate Field	Definition
Minimum	"The least or smallest amount or quantity possible, attainable, or required". If it's a donation amount for a Constituent Number, the smallest dollar value in the database connected to that person's record.
Maximum	"As great, high, or intense as possible or permitted". If it's a donation amount for a Constituent Number, the largest dollar value in the database connected to that person's record.
Average	"The [arithmetic] mean, which is calculated by dividing the sum of the values in the set by their number." If it's a donation amount for a Constituent Number, Salsa CRM calculates the sum of all donation amounts in the database connected to that person's record and divides by the number of donation amounts in the database connected to that person's record.
Sum	"The total amount resulting from the addition of two or more amounts." If it's a donation amount for a Constituent Number, Salsa CRM adds together all donation amounts in the database connected to that person's record.
Count	"determine the total number of (a collection of items)." If it's a donation ID count for a Constituent Number, Salsa CRM identifies all donation amounts in the database connected to that person's record and reports how many.
Distinct Count	Determine the total number of unique items in a collection. If it's a distinct count of countries, Salsa CRM identifies all constituents and their addresses, and only counts each unique Country once.

Definitions in quotes supplied by Dictionary.

Aggregation will also allow you to List items in a single column. For example, aggregation lists multiple groups a constituent belongs to or multiple donations a constituent has made in a single field.

These aggregate fields generated by a query can also be affected by a query's filters. See the Advanced Query Wizard: Create a New General Purpose Query article for instructions on beginning your query. For an overview of the Advanced Query Wizard and its controls, see Advanced Query Wizard Overview.

Running a donation type query, filtering on a specific date range, asking for gift amount and donor name and not using the aggregate filter would return results similar to the example below. Notice the multiple results returned for a few of the constituents.

To list donations from a constituent on one line...

1. In the Available Fields list click once on the field which you would like to filter (in this instance Donation Amount is being used)
2. Click the Gear symbol above the Available Field section.
   
3. Choose Aggregate, and then Sum, Minimum, Maximum, Count, Average or List.  In this example, click Sum.
   
4. The Select Group By Field dialog box opens. Select the field you wish to group by, such as Donor ID to group by donor
5. Click OK.  An italicized field for the aggregate is added to the Output Fields list. The example below shows donors with multiple gifts where a sum of all their donations will display in the Sum Donation Amount by Donor ID column.
   
   NOTE: since there is only one row per donor, the Received Date and Donation Amount will show random values and will not show every value for that Donor ID.
   
6. The results returned in the query will now display donors on an individual line with the sum of their donations displayed.  See the example below. You can see that Salsa CRM summed all of the individual’s donations which were returned in the previous query and listed the sum on one line.