> ## Documentation Index
> Fetch the complete documentation index at: https://code.dcycle.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Query datasets

> Aggregate dataset records, evaluate formulas and recalculate subtotals.

Queries use the authenticated organization's accepted family scope. Use a catalog dataset key or `elastic:<dataset UUID>` for a custom dataset. The dataset schema describes available dimensions, metrics and row-level formula operands.

## Formula evaluation

A formula with `mode: "row"` evaluates each record before applying its `agg` (`sum` by default; `avg` for a mean). A formula with `mode: "aggregate"` evaluates the grouped metric values. The distinction matters: `SUM(value * weight) / SUM(weight)` is a weighted mean; the mean of group means is generally a different result. Division by zero returns `null`.

This example assumes numeric fields `value` and `weight`, and dimension `region` in the dataset schema:

```json theme={"theme":{"light":"github-light","dark":"github-dark"}}
{
  "dimensions": ["region"],
  "metrics": [],
  "formula_metrics": [
    {
      "key": "weighted_sum",
      "label": "Weighted sum",
      "mode": "row",
      "agg": "sum",
      "expr": {"op": "*", "args": [{"field": "value"}, {"field": "weight"}]}
    },
    {
      "key": "weights",
      "label": "Weights",
      "mode": "row",
      "agg": "sum",
      "expr": {"field": "weight"}
    },
    {
      "key": "weighted_mean",
      "label": "Weighted mean",
      "mode": "aggregate",
      "expr": {"op": "/", "args": [{"field": "weighted_sum"}, {"field": "weights"}]}
    }
  ],
  "subtotal_dimensions": [[]],
  "limit": 100
}
```

The numerator and denominator must refer to the same valid observations. If `value` can be missing, exclude that record's weight as well, using an appropriate row formula or input filtering. `avg` ignores null observations; null values are not automatically zero.

## Recalculated subtotals

`subtotal_dimensions` is optional and defaults to `[]`. Each entry is a distinct subset of the query's selected dimensions. An empty subset requests the grand total. For `dimensions: ["region", "product"]`, `[[], ["region"]]` requests both a grand total and regional totals. Requests support at most 16 subsets; raw sample queries reject this option.

The response includes `subtotals`, alongside the existing `columns`, `rows` and `truncated` fields:

```json theme={"theme":{"light":"github-light","dark":"github-dark"}}
{
  "subtotals": [
    {
      "dimensions": [],
      "rows": [{"weighted_sum": "1400", "weights": "100", "weighted_mean": "14"}],
      "truncated": false
    }
  ]
}
```

Each subtotal is recalculated from the source observations with the query's filters, organization scope and dimension row restrictions. It is not a sum of the displayed leaf cells. The leaf result and subtotals run in one SQL statement and share a database snapshot and statement timeout.

`limit` applies independently to each grouping, with its own `truncated` flag. A complete grand total remains valid when the leaf table is truncated. A truncated subtotal grouping can omit groups; clients must not treat an absent group as zero. Request only the groupings needed by the visualization, as each adds aggregation work.

## Temporal and display semantics

For datasets with prorated intervals, a subtotal that removes the period dimension retains the selected time buckets. Amounts are allocated to the selected overlap; a mean counts each source observation once. Aggregate formulas are evaluated after period aggregation.

For external intensity metrics, collapsing an existing period dimension retains its grain and period filters. Monthly and annual denominator records are not added together. Missing selected-period coverage produces a null denominator for division. Existing unsupported combinations of external intensity or conditional attributes with prorated period queries remain unsupported.

Clients should use returned subtotals for means, ratios and chart totals. Comparison windows must remain separate, since windows may overlap. Percentage shares require a complete additive partition; summing means or calculating shares from a truncated sample can produce misleading percentages. Subtotals do not perform unit conversion: incompatible units still require a separating dimension or a separate conversion policy.