Aggregate Results by Data Type

The Group (Aggregate) Results setting allows a user to see grouped results by a particular data type.  Mathematical functions can then be applied to each “group” to get additional data.

One use case for this functionality is to display a list of states and the number of members in each state.

The Group (Aggregate) Results setting is accessible in the Query Builder > Step 3 - Select Result Fields.

  1. In Step 3 - Select Result Fields, add desired result fields.

  2. Click the Group (Aggregate) Results check box.
    The system automatically includes all fields set up under the Step 3 - Select Result Fields section as data points by which the results should be grouped. (See the image, above.)

  1. Next, specify by which data the results should be calculated.

For example, to count the number of members by state, the Group Results setting should be set to group by Address > State (using the "ƒunction" option on the drop-down menu) and count by Memberships > Is Member.

  1. Set the value to be counted by selecting COUNT in the function drop-down next to that result field.

    That field is automatically removed as a "group-by" field. (see image below)

Note: When counting, choose fields that can only have one value per result. For example, each member will only have one value in the "Membership > Is Member" field, but could have multiple values under the "Membership > Type" field. When in doubt, "ID" fields (e.g. ID, Customer ID, Order ID) can always be safely assumed to be unique per result.

Some result fields, such as numerical fields, display options in addition to COUNT, including AVERAGE (displaying a numerical average), SUM (sum of values), MIN (minimum value), and MAX (maximum value).

  1. If the query will have a defined Sort Order, save the query before adding filters to this section. Once the query is saved, only filters that appear in the Step 3 - Select Results Fields section will be available to select in the Step 4 - Define Sort Order section.