Group aggregation functions

Learn about group aggregation functions, or pinned measures.

What if you want to aggregate a value by a specific attribute (for example, show revenue by product)? This is known as a grouped aggregation, but some people call it a pinned measure or level-based measure. You can do this for any aggregation using the grouping functions.

Each of the grouping functions accepts a measure and one or more optional attributes:

formula (measure, [attribute, attribute, ...])

Only the measure value is required. If you supply both a measure and an attribute, the function returns the aggregate of the measure grouped by the attribute(s). You should experiment with only a measure and then with an attribute to see which output best meets your use case.

group_* functions are like their own sub-queries which can be used to aggregate a column at groupings specified in the formula which can be different from the groupings in the search query.

For example:

  1. group_max(balance date) : this formula has no groupings specified, so its like doing a sub-query max balance date. Hence you get last balance date across all the balance dates.

  2. group_max(balance date, balance date) : this formula has balance date as a grouping specified, so its like doing a sub-query [max balance date] [by balance date]. The bucketing to be used for grouping balance data in the sub-query is inherited from the search. Hence you get the last balance date within every segment.

Pivot tables do not support use of group aggregation functions.

List of group functions

Group aggregation functions have names with formats like group_<aggregation>. The group aggregation functions are the following:

group_average

Takes a measure and one or more attributes. Returns the average of the measure grouped by the attribute(s).

group_average (revenue, customer region)

group_count

Takes a measure and one or more attributes. Returns the count of the measure grouped by the attribute(s).

group_count (revenue, customer region)

group_max

Takes a measure and one or more attributes. Returns the maximum of the measure grouped by the attribute(s).

group_max (revenue, customer region)

group_min

Takes a measure and one or more attributes. Returns the minimum of the measure grouped by the attribute(s).

group_min (revenue, customer region)

group_stddev

Takes a measure and one or more attributes. Returns the standard deviation of the measure grouped by the attribute(s).

group_stddev (revenue, customer region)

group_sum

Takes a measure and one or more attributes. Returns the sum of the measure grouped by the attribute(s).

group_sum (revenue, customer region)

group_unique_count

Takes a column name and one or more attributes. Returns the number of unique values in a column, grouped by the attribute(s).

group_unique_count ( product, supplier)

group_variance

Takes a measure and one or more attributes. Returns the variance of the measure grouped by the attribute(s).

group_variance (revenue, customer region)

Flexible aggregation

The group_aggregate function gives you more control over aggregation and filtering.

See Flexible aggregation to learn more about specifying query_groups with this formula.

Limitations of group aggregation functions

Group aggregation functions have the following limitations:

  • You can’t run SpotIQ analysis on a visualization that contains a group aggregation function.

  • You can’t create a KPI chart with a group aggregation function.

  • ThoughtSpot doesn’t support table aggregate headlines for formulas that have group aggregates and are conditional.

  • You can’t run a vs query that also contains a group aggregation function.

  • You can’t run a group aggregation function on a group aggregation function.

Was this page helpful?
×