# Aggregation & Totals ## Overview 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. ## Metadata in the model 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](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-data-object-field-semanticdataobjectfield) and [Semantic Calculated Measurement](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-calculated-measurement-semanticcalcmeasurement) in the Authoring API. Total rows themselves are not model metadata. Subtotals, grand totals, and smart totals are set per query in `options`. ## Query usage ### Aggregation types 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. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } }, "row_grouping": true }, { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM" }, { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_AVG" }, { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_MAX" }, { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_MIN" } ], "options": { "subtotals": true } }, "semanticModelApiName": "test_model" } ``` > **Performance:** Set accurate relationship cardinality to avoid fan-out that double-counts measures across a join. ### Count and count distinct 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. ```json { "structured_semantic_query": { "fields": [ { "expression": { "semantic_field": { "name": "calc_email_clc" } }, "alias": "calc_email_clc", "rowGrouping": false, "semanticAggregationMethod": "SEMANTIC_AGGREGATION_METHOD_COUNT" }, { "expression": { "table_field": { "name": "semantic__Id__c", "table_name": "SemanticAccount__dlm" } }, "alias": "semantic__Id__c", "rowGrouping": false, "semanticAggregationMethod": "SEMANTIC_AGGREGATION_METHOD_COUNT" } ], "options": { "grandTotal": true } }, "semantic_model": { // ... } } ``` ### Auto aggregation 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. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "semantic__AccountSource__c", "table_name": "SemanticAccount__dlm" } }, "row_grouping": true }, { "expression": { "semantic_field": { "name": "summary_level_example" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_AUTO" }, { "expression": { "table_field": { "name": "semantic__Number__c", "table_name": "SemanticAccount__dlm" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM" } ], "options": { "detailed_rows": true, "grand_total": true } }, "semantic_model": { "semanticCalculatedMeasurements": [ { "apiName": "summary_level_example", "label": "summary_level_example", "dataType": "Number", "decimalPlace": 3, "expression": "sum(SemanticAccount__dlm.semantic__Number__c) + 100", "syntax": "Salesforce", "aggregationType": "UserAgg" } // ... ] // ... } } ``` ### User aggregation 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. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "semantic__Name__c", "table_name": "SemanticAccount__dlm" } }, "row_grouping": true }, { "expression": { "table_field": { "name": "semantic__Number__c", "table_name": "SemanticAccount__dlm" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM" }, { "expression": { "calculated_field": { "name": "formula_summary_measurement_example", "expression": "SUM(SemanticAccount__dlm.semantic__Number__c)", "syntax": "Salesforce", "calculated_measure_expression": { "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER" } } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_USER_AGG" } ], "options": { "detailed_rows": false, "subtotals": false, "grand_total": false, "row_counts": false } }, "semantic_model": { // ... } } ``` > **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. ### Subtotals 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. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } }, "row_grouping": true }, { "expression": { "table_field": { "name": "Mailing City", "table_name": "ContactSemanticLayer__dll" } }, "row_grouping": true }, { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM" } ], "options": { "subtotals": true } }, "semanticModelApiName": "test_model" } ``` ### Grand totals 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. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } }, "row_grouping": true }, { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM" }, { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_AVG" } ], "options": { "grand_total": true } }, "semanticModelApiName": "test_model" } ``` ### Row counts 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. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } }, "row_grouping": true }, { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM" } ], "options": { "detailed_rows": true, "subtotals": true, "grand_total": true, "row_counts": true } }, "semanticModelApiName": "test_model" } ``` ### Smart totals 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. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } }, "row_grouping": true }, { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM" } ], "options": { "grand_total": true, "smart_aggregations": true } }, "semanticModelApiName": "test_model" } ``` > **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. ### Unpivot (Measure Names and Measure Values) 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: ```json { "structuredSemanticQuery": { "fields": [ { "expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } }, "alias": "account", "row_grouping": true }, { "expression": { "table_field": { "name": "Mailing City", "table_name": "ContactSemanticLayer__dll" } }, "alias": "city", "row_grouping": true } ], "unpivot": { "column_names_alias": "Measure Names", "column_values_alias": "Measure Values", "query_fields": [ { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "alias": "revenue", "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_AVG" }, { "expression": { "table_field": { "name": "Employees", "table_name": "AccountSemanticLayer__dll" } }, "alias": "employees", "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_AVG" } ] } }, "semanticModelApiName": "test_model" } ``` Without `unpivot`, the grouped result returns one column per measure: | account | city | revenue | employees | | ---------------------- | ------------ | ------: | --------: | | Ms. Makayla Velasquez | West Gina | 76980.0 | 33924.0 | | Mr. Jay Downs Jr. | Mcknightstad | 13839.0 | 11246.0 | | Mr. Preston Hayden DVM | Port Don | 59243.0 | 62478.0 | 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. | account | city | Measure Names | Measure Values | | ---------------------- | ------------ | ------------- | -------------: | | Ms. Makayla Velasquez | West Gina | revenue | 76980.0 | | Ms. Makayla Velasquez | West Gina | employees | 33924.0 | | Mr. Jay Downs Jr. | Mcknightstad | revenue | 13839.0 | | Mr. Jay Downs Jr. | Mcknightstad | employees | 11246.0 | | Mr. Preston Hayden DVM | Port Don | revenue | 59243.0 | | Mr. Preston Hayden DVM | Port Don | employees | 62478.0 | 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. ## Reference | Field (wire name) | Type | Required | Description | | ----------------------------- | ------------------ | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `semantic_aggregation_method` | String (enum) | N | Aggregation method for a measure `QueryField` (for example `SEMANTIC_AGGREGATION_METHOD_SUM`, `_AVG`, `_MIN`, `_MAX`, `_MEDIAN`, `_COUNT`, `_UNIQUE_COUNT`, `_STDDEV`, `_STDDEVP`, `_VAR`, `_VARP`, `_AUTO`, `_USER_AGG`, `_NONE`). When omitted, a plain measure is unaggregated (`_NONE`); use `_AUTO` to apply the field's model-defined aggregation. | | `options.subtotals` | Boolean | N | Add one summary row per grouped dimension. Requires at least one row-grouping field. | | `options.grand_total` | Boolean | N | Add a single summary row aggregating the whole result set. | | `options.smart_aggregations` | Boolean | N | Compute subtotal/grand-total rows in one grouped pass. | | `options.row_counts` | Boolean | N | Include the count of underlying rows per group. | | `unpivot` | QueryFieldsUnpivot | N | Query-level block that rotates selected aggregated measures into name/value rows. | | `column_names_alias` | String | N* | Output column that holds each rotated measure's name. Required when `unpivot` is set. | | `column_values_alias` | String | N* | Output column that holds each rotated measure's value. Required when `unpivot` is set. | | `query_fields` | QueryField[] | N* | Aggregated measures to rotate; each carries its own `semantic_aggregation_method` and an `alias` used as the measure name. At least one is required when `unpivot` is set. | For the full request schema, see [Request Reference](/docs/data/semantic-layer/guide/query-api-in-depth-fields.md). ## Limitations - **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. ## Related - [Semantic Data Object Field](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-data-object-field-semanticdataobjectfield) — Authoring API for a field's default `AggregationType`. - [Semantic Calculated Measurement](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-calculated-measurement-semanticcalcmeasurement) — Authoring API for calculated-measure aggregation. - [Grouping](/docs/data/semantic-layer/guide/query-api-in-depth-grouping.md) — How grouping drives which rows each aggregation summarizes. - [Filtering](/docs/data/semantic-layer/guide/query-api-in-depth-filtering.md) — Aggregate filters (HAVING) apply after aggregation. - [Calculated Fields](/docs/data/semantic-layer/guide/query-api-in-depth-calculated-fields.md) — Define aggregation inside a calculated field for `USER_AGG`. - [Sorting & Limiting](/docs/data/semantic-layer/guide/query-api-in-depth-sorting-limiting.md) — Sorting and limiting apply after an unpivot rotation. - [Shared Dimensions](/docs/data/semantic-layer/guide/query-api-in-depth-shared-dimensions.md) — Unpivot is not supported together with shared dimensions. - [Request Reference](/docs/data/semantic-layer/guide/query-api-in-depth-fields.md) — Complete field schema.