Aggregation & Totals

Aggregation controls how a semantic query summarizes measure values across the rows in each group. Set the aggregation for a field with semantic_aggregation_method on a QueryField. When you group by one or more dimensions, every non-grouped measure collapses to a single value per group using the method you specify (sum, average, minimum, maximum, median, count, count distinct, standard deviation, or variance).

Totals add summary rows on top of the grouped result. Subtotals produce one summary row per grouped dimension, and grand totals produce a single summary row for the whole result set. Both are turned on with boolean flags in options.

The single most important thing to know: the aggregation you set on a field also drives its subtotal and grand-total values. A field aggregated as SEMANTIC_AGGREGATION_METHOD_AVG is averaged in its total row, a field aggregated as SEMANTIC_AGGREGATION_METHOD_SUM is summed, and so on. Aggregation and totals are two halves of the same summarization step.

A measurement carries a default aggregation method in the Semantic Data Model. When you author a SemanticDataObjectField with a Measurement data role, or a SemanticCalculatedMeasurement, you set its AggregationType (for example Sum, Average, Count, Median, UserAgg, or None). Request SEMANTIC_AGGREGATION_METHOD_AUTO to use this model-defined default; for calculated-measurement references, omitting the method also resolves to the model default. For a plain measure table_field, omitting the method does not fall back to the model default — it leaves the field unaggregated (SEMANTIC_AGGREGATION_METHOD_NONE). For how to define aggregation on a field, see Semantic Data Object Field and Semantic Calculated Measurement in the Authoring API.

Total rows themselves are not model metadata. Subtotals, grand totals, and smart totals are set per query in options.

Set semantic_aggregation_method on any measure QueryField to choose how it is aggregated within each group. Supported values are SEMANTIC_AGGREGATION_METHOD_SUM, _AVG, _MIN, _MAX, _MEDIAN, _COUNT, _UNIQUE_COUNT (count distinct), _STDDEV, _STDDEVP, _VAR, _VARP, _AUTO, _USER_AGG, and _NONE. Repeat the same field with different methods to return several aggregations of one measure side by side.

Performance: Set accurate relationship cardinality to avoid fan-out that double-counts measures across a join.

Use SEMANTIC_AGGREGATION_METHOD_COUNT to count values and SEMANTIC_AGGREGATION_METHOD_UNIQUE_COUNT to count distinct values. Count aggregations apply to non-numeric fields as well as numeric ones, so you can count text dimensions, IDs, and calculated fields.

Set SEMANTIC_AGGREGATION_METHOD_AUTO to apply the field’s model-defined default aggregation. This is most useful with a semantic_field reference: the query defers the choice of method to the AggregationType authored on the measurement in the model. In the following example, summary_level_example is a model calculated measurement whose default aggregation resolves at query time.

Set SEMANTIC_AGGREGATION_METHOD_USER_AGG when the aggregation is already expressed inside the formula. The query treats the field as pre-aggregated and applies no further aggregation on top of it. Use this for a calculated field whose expression already contains an aggregate function, such as SUM(...). USER_AGG is also valid for a formula that evaluates to a static numeric value with no field references — for example "123", INT(1.0), or PI() + 4 — which is treated as already aggregated.

Performance: Move conditions out of aggregate_filter (HAVING) into the row-level filter where possible. HAVING runs after aggregation and cannot reduce the data scanned.

Set "subtotals": true in options to add one summary row per grouped dimension. Subtotals require at least one row-grouping field, and each measure’s subtotal uses that field’s own aggregation method.

Set "grand_total": true in options to add a single summary row that aggregates every value in the result set. Each measure’s grand-total value uses that field’s aggregation method.

Set "row_counts": true in options to include a count of the underlying rows for each group in the result. Combine it with subtotals and grand totals to return counts alongside the summary rows.

Set "smart_aggregations": true in options alongside subtotals or grand totals to compute the total rows in a single grouped pass rather than as separate aggregations. Use it with the same grouping and aggregated fields you already query.

Performance: Set smart_aggregations to true when requesting subtotals or grand totals so all total levels are computed in one grouped pass instead of multiple aggregation passes.

Use the query-level unpivot block to rotate one or more aggregated measures from columns into rows — the Semantic Layer implementation of Tableau’s Measure Names / Measure Values. Instead of returning each measure in its own column, the result returns two generated columns: one that holds each measure’s name and one that holds its value. This is useful for visualizations that plot several measures along a single axis.

Set unpivot on structuredSemanticQuery (a sibling of fields and options) with three properties: column_names_alias (the output column that holds each measure’s name), column_values_alias (the output column that holds each measure’s value), and query_fields (the list of aggregated measures to rotate). Each entry in query_fields is a measure field with its semantic_aggregation_method and an alias that becomes the measure’s name in the output. The measures to rotate belong only in query_fields — don’t also list them in the top-level fields. Group-by dimensions stay in fields with row_grouping: true; they pass through unchanged alongside the two generated columns.

The following query groups by account and city and rotates two averaged measures (revenue and employees) into Measure Names / Measure Values rows:

Without unpivot, the grouped result returns one column per measure:

With unpivot, each measure becomes its own row: column_names_alias (Measure Names) holds the measure’s alias and column_values_alias (Measure Values) holds its value.

For every grouped row before the rotation, the query returns one row per query_fields entry — so three grouped rows and two rotated measures produce six result rows. Filters apply before the rotation; sorting and limiting apply after it, so a limit caps the expanded row count rather than the pre-rotation row count.

For the full request schema, see Request Reference.

• Subtotals require row grouping. Set at least one field’s row_grouping (or grouping: "ROW_GROUPING") to true; requesting subtotals with no grouping field is rejected.
• Totals require aggregated fields. Subtotals and grand totals need at least one measure with an aggregation method; a totals request with no aggregated field is rejected unless row_counts is set.
• A field cannot carry both its own aggregation and a separate totals aggregation. When a select field is already aggregated, you cannot request an additional, different totals aggregation for it.
• USER_AGG is not supported for logical table fields. Use USER_AGG only on calculated fields or static numeric expressions, not on fields of a logical table.
• Row counts and multi-table totals do not combine in an auto-join. In an auto-join, row counts are not supported, and subtotals or grand totals cannot be requested together with detail_rows: true.
• The aggregation method must be valid for the field’s data type. For example, statistical methods such as standard deviation and variance apply to numeric fields.
• Unpivot inputs must be numeric. Every measure listed in query_fields must resolve to a numeric value; a non-numeric input is rejected. A null input is returned as a row with a null value.
• You cannot filter on the generated unpivot columns. Filtering by the column_names_alias or column_values_alias column is not supported.
• Unpivot is not supported together with shared dimensions. A query cannot combine an unpivot block with shared dimensions.

• Semantic Data Object Field — Authoring API for a field’s default AggregationType.
• Semantic Calculated Measurement — Authoring API for calculated-measure aggregation.
• Grouping — How grouping drives which rows each aggregation summarizes.
• Filtering — Aggregate filters (HAVING) apply after aggregation.
• Calculated Fields — Define aggregation inside a calculated field for USER_AGG.
• Sorting & Limiting — Sorting and limiting apply after an unpivot rotation.
• Shared Dimensions — Unpivot is not supported together with shared dimensions.
• Request Reference — Complete field schema.