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 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.

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.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "Account Name",
8            "table_name": "AccountSemanticLayer__dll"
9          }
10        },
11        "row_grouping": true
12      },
13      {
14        "expression": {
15          "table_field": {
16            "name": "Annual Revenue",
17            "table_name": "AccountSemanticLayer__dll"
18          }
19        },
20        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
21      },
22      {
23        "expression": {
24          "table_field": {
25            "name": "Annual Revenue",
26            "table_name": "AccountSemanticLayer__dll"
27          }
28        },
29        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_AVG"
30      },
31      {
32        "expression": {
33          "table_field": {
34            "name": "Annual Revenue",
35            "table_name": "AccountSemanticLayer__dll"
36          }
37        },
38        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_MAX"
39      },
40      {
41        "expression": {
42          "table_field": {
43            "name": "Annual Revenue",
44            "table_name": "AccountSemanticLayer__dll"
45          }
46        },
47        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_MIN"
48      }
49    ],
50    "options": {
51      "subtotals": true
52    }
53  },
54  "semanticModelApiName": "test_model"
55}

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.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "semantic_field": {
7            "name": "calc_email_clc"
8          }
9        },
10        "alias": "calc_email_clc",
11        "rowGrouping": false,
12        "semanticAggregationMethod": "SEMANTIC_AGGREGATION_METHOD_COUNT"
13      },
14      {
15        "expression": {
16          "table_field": {
17            "name": "semantic__Id__c",
18            "table_name": "SemanticAccount__dlm"
19          }
20        },
21        "alias": "semantic__Id__c",
22        "rowGrouping": false,
23        "semanticAggregationMethod": "SEMANTIC_AGGREGATION_METHOD_COUNT"
24      }
25    ],
26    "options": {
27      "grandTotal": true
28    }
29  },
30  "semantic_model": {
31    // ...
32  }
33}

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.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "semantic__AccountSource__c",
8            "table_name": "SemanticAccount__dlm"
9          }
10        },
11        "row_grouping": true
12      },
13      {
14        "expression": {
15          "semantic_field": {
16            "name": "summary_level_example"
17          }
18        },
19        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_AUTO"
20      },
21      {
22        "expression": {
23          "table_field": {
24            "name": "semantic__Number__c",
25            "table_name": "SemanticAccount__dlm"
26          }
27        },
28        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
29      }
30    ],
31    "options": {
32      "detailed_rows": true,
33      "grand_total": true
34    }
35  },
36  "semantic_model": {
37    "semanticCalculatedMeasurements": [
38      {
39        "apiName": "summary_level_example",
40        "label": "summary_level_example",
41        "dataType": "Number",
42        "decimalPlace": 3,
43        "expression": "sum(SemanticAccount__dlm.semantic__Number__c) + 100",
44        "syntax": "Salesforce",
45        "aggregationType": "UserAgg"
46      }
47      // ...
48    ]
49    // ...
50  }
51}

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.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "semantic__Name__c",
8            "table_name": "SemanticAccount__dlm"
9          }
10        },
11        "row_grouping": true
12      },
13      {
14        "expression": {
15          "table_field": {
16            "name": "semantic__Number__c",
17            "table_name": "SemanticAccount__dlm"
18          }
19        },
20        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
21      },
22      {
23        "expression": {
24          "calculated_field": {
25            "name": "formula_summary_measurement_example",
26            "expression": "SUM(SemanticAccount__dlm.semantic__Number__c)",
27            "syntax": "Salesforce",
28            "calculated_measure_expression": {
29              "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
30            }
31          }
32        },
33        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_USER_AGG"
34      }
35    ],
36    "options": {
37      "detailed_rows": false,
38      "subtotals": false,
39      "grand_total": false,
40      "row_counts": false
41    }
42  },
43  "semantic_model": {
44    // ...
45  }
46}

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.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "Account Name",
8            "table_name": "AccountSemanticLayer__dll"
9          }
10        },
11        "row_grouping": true
12      },
13      {
14        "expression": {
15          "table_field": {
16            "name": "Mailing City",
17            "table_name": "ContactSemanticLayer__dll"
18          }
19        },
20        "row_grouping": true
21      },
22      {
23        "expression": {
24          "table_field": {
25            "name": "Annual Revenue",
26            "table_name": "AccountSemanticLayer__dll"
27          }
28        },
29        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
30      }
31    ],
32    "options": {
33      "subtotals": true
34    }
35  },
36  "semanticModelApiName": "test_model"
37}

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.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "Account Name",
8            "table_name": "AccountSemanticLayer__dll"
9          }
10        },
11        "row_grouping": true
12      },
13      {
14        "expression": {
15          "table_field": {
16            "name": "Annual Revenue",
17            "table_name": "AccountSemanticLayer__dll"
18          }
19        },
20        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
21      },
22      {
23        "expression": {
24          "table_field": {
25            "name": "Annual Revenue",
26            "table_name": "AccountSemanticLayer__dll"
27          }
28        },
29        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_AVG"
30      }
31    ],
32    "options": {
33      "grand_total": true
34    }
35  },
36  "semanticModelApiName": "test_model"
37}

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.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "Account Name",
8            "table_name": "AccountSemanticLayer__dll"
9          }
10        },
11        "row_grouping": true
12      },
13      {
14        "expression": {
15          "table_field": {
16            "name": "Annual Revenue",
17            "table_name": "AccountSemanticLayer__dll"
18          }
19        },
20        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
21      }
22    ],
23    "options": {
24      "detailed_rows": true,
25      "subtotals": true,
26      "grand_total": true,
27      "row_counts": true
28    }
29  },
30  "semanticModelApiName": "test_model"
31}

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.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "Account Name",
8            "table_name": "AccountSemanticLayer__dll"
9          }
10        },
11        "row_grouping": true
12      },
13      {
14        "expression": {
15          "table_field": {
16            "name": "Annual Revenue",
17            "table_name": "AccountSemanticLayer__dll"
18          }
19        },
20        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
21      }
22    ],
23    "options": {
24      "grand_total": true,
25      "smart_aggregations": true
26    }
27  },
28  "semanticModelApiName": "test_model"
29}

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.

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.

For the full request schema, see Request Reference.

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.

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.
Request Reference — Complete field schema.