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.

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. If you would like to create a nested group aggregation function, you can do so by first saving the answer with the first level of the group function as a View, then using the View as the data source for a second answer with the second level of the group function.

Was this page helpful?
×