Aggregating Query Results

In the Query Builder, “aggregation” is the process of calculating a set of data to return a single value (count) or to categorize/group returned values. Using this, a query can be created to show the aggregate number of records matching query criteria rather than listing each individual result. 

This functionality is available in the Query Builder > Step 3 – Select Result Fields. For examples of how the different aggregate functions can be used, see Use Cases below.

For more on building queries in general, see Creating a New Query.

Aggregate Result Configurations

The following articles describe a certain type of result from your aggregate query--for example, how to return a single value for a set of results.

Use Cases

The following use cases offer specific examples of how aggregate functionality can be used.

Note: In each scenario, we'll begin by navigating to the Query Builder.

Use Case:  List Event Sessions Showing Number of Registrants by Session

This scenario creates a query listing event sessions and counts registrants per session.

  1. Select a query pulling a list of active session registrants. You might choose to create a copy of the existing "List of Active Session Registrants" query as a starting point.

  2. Under Step 3 - Select Results Fields:

    1. add the fields Session > Name and Customer ID.

    2. Click the Group (Aggregate) Results check box.
      The system automatically sets all fields in the Step 3 - Select Results Fields section as “group by” field.

    3. Still in the Step 3 - Select Results Fields section, choose which value should be counted by clicking the function drop-down field to the left of that value and selecting COUNT.

  1. Complete the query and click Save.

  2. Run the completed query.

If fields in Step 2 - Define Filter Criteria section were set to “Ask at Run Time,” the user can view different aggregate results based on the values they search on.  In the example below, the user can specify from which event the sessions should be listed and session registrants counted.

In the image below, results are set to group by Session Name and count by Customer ID. Query results will display a table listing sessions with a corresponding column displaying a registrant count next to each session.

In the image below, data is grouped by session in the Session Name column and counted in the Count of Customer ID column. As always, users can sort this data by clicking the column header.

Use Case: Applying Mathematical Functions to Numerical Fields

This scenario creates a query that applies a function–in this case, the sum–to numerical results. In particular, this query pulls a list of customers with a balance owing and calculates the total amount due per customer.

  1. Select or create a query pulling a list of purchases with a balance greater than zero.

  2. Under Step 3 - Select Results Fields

    1. Add the Customer > Record Number and Balance fields.

    2. Click the Group (Aggregate) Results check box.
      The system automatically sets all fields in the Step 3 - Select Results Fields section as “group by” fields.

    3. Click the function drop-down field next to the “Balance” field and select SUM.

  3. Complete the query and click Save.

  4. Run the completed query.

The results list each customer by Record Number and provide a total of of that customer’s balances due.

Use Case: Aggregating by Custom Field Value

This scenario creates a query listing a custom field value—in this case, “Shoe Size”—and the count of customers in the system matching values in that custom field. This configuration works well with single-select custom fields, such as drop-down or radio button fields. It is not recommended for multi-select custom fields or dates.

  1. Select or create a query pulling a list of a custom field value. Ensure the custom field you chose is added to the Step 2 - Define Filter Criteria section.

  2. Under the Step 3 - Select Results Fields:

    1. Click the Add Count (Number of Results) icon to add the COUNT > (Number of Results) field.

Results are set to group by the custom field value selected.

Use Case: Creating a Subquery Using Aggregate Configuration

In this scenario, a query is created to find donors who have contributed more than $50,000 so they can be contacted.

Creating the Query Listing Donors Giving Over a Certain Amount

This query lists all customers who have donated more than $50,000.

  1. In Step 1 - Select Query Target, set the Query Target to Fund Purchase.

  2. In Step 2 - Define Filter Criteria, using the Price Amount field, set this criterion to search for values greater than “0.”

  3. In Step 3 - Select Results Fields,

    1. Add the Record Number and Name fields.

    2. Click the Group (Aggregate) Results check box—all current result fields will be included under the Group (Aggregate) Results section.

    3. Click the Additional Filter Criteria for Groups check box.

      1. Under the Additional Filter Criteria […] section, click the Add icon and add the Price Amount field. Set this criterion to search for values greater than 50000.

  4. Complete and Save the query.

Creating a Donor List Using a Subquery Configuration

  1. In Step 1 - Select Query Target, create a query using the Query Target, “Individual.”

  2. In Step 2 - Define Filter Criteria section, configure a subquery field using the query created in the previous section, which pulled a list of donors over $50k.

  3. In Step 3 - Select Results Fields section, set the result fields to display First Name and Last Name.

  4. Save the query. When run, this query lists members who donated over the amount indicated in the associated subquery.

Aggregate Query Limitations

Certain query fields, as designated by a calculator icon, cannot be used with the new aggregate grouping functionality. These fields are populated with data that is not in the actual database, but is calculated when the query is run: Impexium runs an expression to populate the virtual field with data each time the query is run.