Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 35 ยท Tables and MergeTree Engines

Summing and Aggregating Engines

Pre-aggregate data in ClickHouse with SummingMergeTree and AggregatingMergeTree: sum columns on merge, store aggregate states, and read them with -State and -Merge.

Raw events are valuable, but dashboards rarely need them: they need totals per day, per customer, per page. Scanning billions of events for every dashboard load is wasteful. ClickHouse can pre-aggregate during merges: SummingMergeTree adds up numeric columns that share a key, and AggregatingMergeTree stores partial aggregate states (averages, unique counts, quantiles) and combines them. These tables are usually fed by materialized views from a raw table, covered in the next chapter.

ClickHouse cannot run on this site. The engines and the -State/-Merge functions are read-only. The runnable blocks model the same arithmetic with SQLite: rolling up rows with the same key, and why an average of averages is wrong while sum and count states combine correctly.

SummingMergeTree

SQL
CREATE TABLE daily_views
(
    day      Date,
    page     LowCardinality(String),
    views    UInt64,
    bytes    UInt64
)
ENGINE = SummingMergeTree
ORDER BY (day, page);

When parts merge, rows with the same ORDER BY key collapse into one row, and the numeric columns not in the key are summed. You insert small increments and let merges roll them up:

SQL
INSERT INTO daily_views VALUES ('2026-05-01', '/home', 10, 5000);
INSERT INTO daily_views VALUES ('2026-05-01', '/home',  7, 3100);
-- after a merge: ('2026-05-01', '/home', 17, 8100)

Like the Replacing engine, this happens eventually. So queries always aggregate anyway:

SQL
SELECT day, page, sum(views) AS views, sum(bytes) AS bytes
FROM daily_views
GROUP BY day, page;

You can list which columns to sum, for example SummingMergeTree((views, bytes)); by default all numeric non-key columns are summed. Non-numeric columns keep an arbitrary value from the merged rows, so avoid depending on them.

SQL Stand-in: the roll-up a merge performs (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Why averages cannot just be summed

Sum works because sum(a) + sum(b) = sum(a and b). An average does not: the average of two averages ignores group sizes. If one part has 1 row averaging 100 and another has 99 rows averaging 200, the true average is 199, not 150. To combine averages you need to keep the sum and count and divide at the end.

SQL Stand-in: average of averages is wrong (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

That is the problem AggregatingMergeTree solves for every aggregate function, not only sums.

AggregatingMergeTree

This engine stores aggregate function states: compact intermediate values that can be merged. The column type is AggregateFunction(func, argument types).

SQL
CREATE TABLE page_stats
(
    day      Date,
    page     LowCardinality(String),
    visitors AggregateFunction(uniq, UInt64),
    avg_ms   AggregateFunction(avg, UInt32),
    p95_ms   AggregateFunction(quantile(0.95), UInt32)
)
ENGINE = AggregatingMergeTree
ORDER BY (day, page);

You write states with the -State combinator and read them back with -Merge:

SQL
-- write: INSERT ... SELECT with -State functions
INSERT INTO page_stats
SELECT toDate(event_time) AS day, page,
       uniqState(user_id),
       avgState(duration_ms),
       quantileState(0.95)(duration_ms)
FROM events
GROUP BY day, page;

-- read: -Merge combines the states of all parts and rows
SELECT day, page,
       uniqMerge(visitors)         AS visitors,
       avgMerge(avg_ms)            AS avg_ms,
       quantileMerge(0.95)(p95_ms) AS p95_ms
FROM page_stats
GROUP BY day, page;

A state for avg internally holds the running sum and count; for uniq it holds a sketch of the distinct values; for quantile a compact sample. Merging two states gives the same answer as if you had aggregated all the raw rows together, which is exactly what makes pre-aggregation correct. See aggregate functions and combinators.

There is also SimpleAggregateFunction(func, type) for functions whose state is just the value (sum, min, max, any, anyLast, groupArrayArray). It is simpler: you write plain values and read them with the plain function.

SQL
CREATE TABLE minmax
(
    day  Date,
    host String,
    lo   SimpleAggregateFunction(min, UInt32),
    hi   SimpleAggregateFunction(max, UInt32)
)
ENGINE = AggregatingMergeTree ORDER BY (day, host);

Modelling states with SQLite

SQLite has no state types, but we can store the sum and count (the state of avg) and merge later. This is the same shape ClickHouse uses.

SQL Stand-in: avg stored as (sum, count) and merged (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Choosing between the two

Need Engine
Totals, counts (additive numbers) SummingMergeTree
Distinct counts, averages, quantiles, min/max, any aggregate AggregatingMergeTree
Keep the latest row, not combine ReplacingMergeTree (lesson)

Remember that both feed a table of rolled-up data, not a replacement for raw data. A popular design keeps raw events with a short TTL and rolled-up tables for long history.

Common beginner mistakes

  • Querying the table without GROUP BY, forgetting rows are not merged yet.
  • Putting an average directly in a SummingMergeTree: sums of averages are meaningless. Store sum and count, or use AggregatingMergeTree.
  • Mixing up uniqState (write) and uniqMerge (read), or using the plain uniq on a state column.
  • Changing the key after loading data.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does SummingMergeTree do when parts are merged?

    Rows with equal ORDER BY keys are combined and non-key numeric columns are summed.
  2. 2. Why is averaging averages incorrect?

    You must combine sums and counts (or an avg state) and divide at the end.
  3. 3. Which combinator writes an aggregate state, and which reads it?

    uniqState produces a state to store; uniqMerge combines stored states into a result.
  4. 4. Why do queries on a SummingMergeTree table still use GROUP BY?

    Until a merge happens, several partial rows for a key may exist, so aggregate at query time.

Summary

  • SummingMergeTree adds numeric columns of rows with the same key during merges.
  • AggregatingMergeTree stores aggregate states (-State) and combines them (-Merge) for any aggregate function.
  • Merges are eventual, so queries still use GROUP BY with sum or -Merge.
  • Combine sums and counts, never averages; use SimpleAggregateFunction for min, max and sum.

Next: TTL, codecs and mutations