AX2052

Specifying an alternate aggregation method for a field definition

By default, when you query data via an Axiom query, the results are aggregated as follows:

  • Numeric columns are summed based on the Sum by level for the query. "Numeric" in this context means any column with a data type of Integer (all types) or Numeric. The following exceptions apply:
    • Integer key columns and validated columns are never summed; the maximum value is always returned.
    • Non-key, non-validated numeric columns in reference tables are only summed if the reference table is the primary table for the query. If a data table is the primary table, then these columns are not summed and the maximum value is returned instead.
  • All other column types return the maximum value. In most cases, the Sum by for the query is set so that there is only one possible value per record.

In certain cases you may want to return different information, such as a count of records. This option is specified at the database column level, in the field definition. The syntax for specifying an alternate aggregation is as follows:

AxAggregate(AggregationType)ColumnName

NOTE: The entire field definition entry cannot have any spaces.

IMPORTANT: The aggregation types of Avg and DistinctCount cannot be used with column filters, either on the same field definition entry or on different field definition entries.

Aggregation types

The following aggregation types are supported for use with AxAggregate:

  • Count: Returns the count of records in the query set.
  • DistinctCount: Returns the count of unique records in the query set. For example, if the query returns two records that both have a value of 10, Count will return 2 but DistinctCount will return 1.
  • Min: Returns the minimum record in the query set.
  • Max: Returns the maximum record in the query set.
  • Sum: Returns the sum of records in the query set. Does not apply when querying string or date columns.
  • Avg: Returns the average value of records in the query set. Does not apply when querying string or date columns.

The column name can use fully-qualified syntax, column-only syntax, or alias names.

For example, if you want to return a count of records in the DEPT table, you would enter the following into the field definition row or column:

AxAggregate(Count)DEPT

NOTE: Alternate aggregation is not supported when the primary table for the query is a system table, such as Axiom.Aliases.

Using column filters with AxAggregate

You can use column filters and AxAggregate together on a field definition entry, so that the alternate aggregation is applied to a subset of data (as defined by the filter), instead of all the data returned by that column. For example:

AxAggregate(Min)GL2018.m1;dept.region='US West'

This example returns the minimum values for GL2018.m1, for departments that belong to the US West region only, at the "sum by" level of the query. This might be used in a report that has several columns like this, each one using a different region filter for purposes of comparing the region values side by side.

All aggregation types can be used in combination with column filters, except for Avg and DistinctCount.

TIP: As an alternative to Avg, it is possible to calculate the average by using two columns with Sum aggregation and Count aggregation, and then use a formula in the calc method to divide the sum by the count.

AX2052