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

> Syntax for the CREATE AGGREGATING INDEX command in Firebolt.

# CREATE AGGREGATING INDEX

Creates an aggregating index: a precomputed, automatically maintained rollup of a `GROUP BY` with aggregate functions. The optimizer substitutes it for the base-table scan when a query's grouping structure matches.

This page is the syntax reference. For how the index is maintained, the rule that a query must group by or filter on the leading grouping column, tuning, and limitations, see [Aggregating index](/performance-and-observability/storage-and-indexing/aggregating-index).

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE AGGREGATING INDEX <index_name> ON <table_name> (
  <grouping_element> [, ...],
  <aggregation_element> [, ...]
);
```

## Parameters

| Parameter               | Description                                                                                                                                                                                       |
| :---------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `<index_name>`          | A unique name for the aggregating index.                                                                                                                                                          |
| `<table_name>`          | The table on which the index is created.                                                                                                                                                          |
| `<grouping_element>`    | One or more columns or expressions used as grouping keys (dimensions). Order matters: a query must reference the leading key to use the index.                                                    |
| `<aggregation_element>` | One or more [aggregate functions](/reference-sql/functions-reference/aggregation) (`SUM`, `COUNT`, `AVG`, …) applied to column expressions. A `COUNT(*)` is added automatically if not specified. |

## Example

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE AGGREGATING INDEX sales_agg_index ON sales (
  product_id,
  region,
  SUM(sales_amount),
  COUNT(DISTINCT order_id)
);
```

## See also

* [Aggregating index](/performance-and-observability/storage-and-indexing/aggregating-index) covers the mechanism, usage rules, best practices, and limitations.
* [`CREATE INDEX`](/reference-sql/commands/data-definition/create-index), [`DROP INDEX`](/reference-sql/commands/data-definition/drop-index), and [`EXPLAIN`](/reference-sql/commands/queries/explain).
* [`information_schema.indexes`](/reference-sql/information-schema/indexes).