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

> SummingMergeTree inherits from the MergeTree engine. Its key feature is the ability to automatically sum numeric data during part merges.

# SummingMergeTree table engine

The engine inherits from [MergeTree](/docs/reference/engines/table-engines/mergetree-family/mergetree). The difference is that when merging data parts for `SummingMergeTree` tables ClickHouse replaces all the rows with the same primary key (or more accurately, with the same [sorting key](/docs/reference/engines/table-engines/mergetree-family/mergetree)) with one row which contains summed values for the columns with the numeric data type. If the sorting key is composed in a way that a single key value corresponds to large number of rows, this significantly reduces storage volume and speeds up data selection.

We recommend using the engine together with `MergeTree`. Store complete data in `MergeTree` table, and use `SummingMergeTree` for aggregated data storing, for example, when preparing reports. Such an approach will prevent you from losing valuable data due to an incorrectly composed primary key.

<h2 id="creating-a-table">
  Creating a table
</h2>

```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = SummingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]
```

For a description of request parameters, see [request description](/docs/reference/statements/create/table).

<h3 id="parameters-of-summingmergetree">
  Parameters of SummingMergeTree
</h3>

<h4 id="columns">
  Columns
</h4>

`columns` - a tuple with the names of columns where values will be summed. Optional parameter.
The columns must be of a numeric type and must not be in the partition or sorting key.

If `columns` is not specified, ClickHouse summarizes the values in all columns with a numeric data type that are not in the sorting key.

<h3 id="query-clauses">
  Query clauses
</h3>

When creating a `SummingMergeTree` table the same [clauses](/docs/reference/engines/table-engines/mergetree-family/mergetree) are required, as when creating a `MergeTree` table.

<details markdown="1">
  <summary>Deprecated Method for Creating a Table</summary>

  <Note>
    Do not use this method in new projects and, if possible, switch the old projects to the method described above.
  </Note>

  ```sql theme={null}
  CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  (
      name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
      name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
      ...
  ) ENGINE [=] SummingMergeTree(date-column [, sampling_expression], (primary, key), index_granularity, [columns])
  ```

  All of the parameters excepting `columns` have the same meaning as in `MergeTree`.

  * `columns` — tuple with names of columns values of which will be summed. Optional parameter. For a description, see the text above.
</details>

<h2 id="usage-example">
  Usage example
</h2>

Consider the following table:

```sql theme={null}
CREATE TABLE summtt
(
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key
```

Insert data to it:

```sql theme={null}
INSERT INTO summtt VALUES(1,1),(1,2),(2,1)
```

ClickHouse may sum all the rows not completely ([see below](#data-processing)), so we use an aggregate function `sum` and `GROUP BY` clause in the query.

```sql theme={null}
SELECT key, sum(value) FROM summtt GROUP BY key
```

```text theme={null}
┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘
```

<h2 id="data-processing">
  Data processing
</h2>

When data are inserted into a table, they are saved as-is. ClickHouse merges the inserted parts of data periodically and this is when rows with the same primary key are summed and replaced with one for each resulting part of data.

ClickHouse can merge the data parts so that different resulting parts of data can consist rows with the same primary key, i.e. the summation will be incomplete. Therefore (`SELECT`) an aggregate function [sum()](/docs/reference/functions/aggregate-functions/sum) and `GROUP BY` clause should be used in a query as described in the example above.

<h3 id="common-rules-for-summation">
  Common rules for summation
</h3>

The values in the columns with the numeric data type are summed. The set of columns is defined by the parameter `columns`.

If the values were 0 in all of the columns for summation, the row is deleted.

If column is not in the primary key and is not summed, an arbitrary value is selected from the existing ones.

The values are not summed for columns in the primary key.

<h3 id="the-summation-in-the-aggregatefunction-columns">
  The summation in the AggregateFunction columns
</h3>

For columns of [AggregateFunction type](/docs/reference/data-types/aggregatefunction) ClickHouse behaves as [AggregatingMergeTree](/docs/reference/engines/table-engines/mergetree-family/aggregatingmergetree) engine aggregating according to the function.

<h3 id="nested-structures">
  Nested structures
</h3>

Table can have nested data structures that are processed in a special way.

If the name of a nested table ends with `Map` and it contains at least two columns that meet the following criteria:

* the first column is numeric `(*Int*, Date, DateTime)` or a string `(String, FixedString)`, let's call it `key`,
* the other columns are arithmetic `(*Int*, Float32/64)`, let's call it `(values...)`,

then this nested table is interpreted as a mapping of `key => (values...)`, and when merging its rows, the elements of two data sets are merged by `key` with a summation of the corresponding `(values...)`.

Examples:

```text theme={null}
DROP TABLE IF EXISTS nested_sum;
CREATE TABLE nested_sum
(
    date Date,
    site UInt32,
    hitsMap Nested(
        browser String,
        imps UInt32,
        clicks UInt32
    )
) ENGINE = SummingMergeTree
PRIMARY KEY (date, site);

INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['Firefox', 'Opera'], [10, 5], [2, 1]);
INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['Chrome', 'Firefox'], [20, 1], [1, 1]);
INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['IE'], [22], [0]);
INSERT INTO nested_sum VALUES ('2020-01-01', 10, ['Chrome'], [4], [3]);

OPTIMIZE TABLE nested_sum FINAL; -- emulate merge

SELECT * FROM nested_sum;
┌───────date─┬─site─┬─hitsMap.browser───────────────────┬─hitsMap.imps─┬─hitsMap.clicks─┐
│ 2020-01-01 │   10 │ ['Chrome']                        │ [4]          │ [3]            │
│ 2020-01-01 │   12 │ ['Chrome','Firefox','IE','Opera'] │ [20,11,22,5] │ [1,3,0,1]      │
└────────────┴──────┴───────────────────────────────────┴──────────────┴────────────────┘

SELECT
    site,
    browser,
    impressions,
    clicks
FROM
(
    SELECT
        site,
        sumMap(hitsMap.browser, hitsMap.imps, hitsMap.clicks) AS imps_map
    FROM nested_sum
    GROUP BY site
)
ARRAY JOIN
    imps_map.1 AS browser,
    imps_map.2 AS impressions,
    imps_map.3 AS clicks;

┌─site─┬─browser─┬─impressions─┬─clicks─┐
│   12 │ Chrome  │          20 │      1 │
│   12 │ Firefox │          11 │      3 │
│   12 │ IE      │          22 │      0 │
│   12 │ Opera   │           5 │      1 │
│   10 │ Chrome  │           4 │      3 │
└──────┴─────────┴─────────────┴────────┘
```

When requesting data, use the [sumMap(key, value)](/docs/reference/functions/aggregate-functions/sumMap) function for aggregation of `Map`.

For nested data structure, you do not need to specify its columns in the tuple of columns for summation.

<h3 id="tuple-element-aggregation">
  Tuple element aggregation
</h3>

When the `allow_tuple_element_aggregation` setting is enabled, `Tuple` columns are recursively flattened so that each leaf element participates in summation independently. This allows you to store multiple metrics in a single `Tuple` column and have them summed element-wise during merges.

The same rules apply to the flattened sub-columns as to regular columns:

* Only numeric sub-columns are summed.
* Sub-columns that belong to a `Tuple` in the sorting key or partition key are excluded from summation.
* If `columns` is specified, only sub-columns of the listed `Tuple` columns are summed.
* If all numeric sub-columns of a row are zero after summation, the row is deleted.

<Note>
  This setting is immutable and must be specified at table creation time.
</Note>

```sql theme={null}
CREATE TABLE summing_tuples
(
    key UInt32,
    metrics Tuple(
        impressions UInt64,
        clicks UInt64,
        nested Tuple(
            conversions UInt64
        )
    )
) ENGINE = SummingMergeTree()
ORDER BY key
SETTINGS allow_tuple_element_aggregation = 1;

INSERT INTO summing_tuples VALUES (1, (100, 10, (1)));
INSERT INTO summing_tuples VALUES (1, (200, 20, (3)));

OPTIMIZE TABLE summing_tuples FINAL;

SELECT key, metrics.impressions, metrics.clicks, metrics.nested.conversions FROM summing_tuples;
```

```text theme={null}
┌─key─┬─metrics.impressions─┬─metrics.clicks─┬─metrics.nested.conversions─┐
│   1 │                 300 │             30 │                          4 │
└─────┴─────────────────────┴────────────────┴────────────────────────────┘
```

<h2 id="related-content">
  Related content
</h2>

* Blog: [Using Aggregate Combinators in ClickHouse](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)