Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 35 ยท Aggregation and Materialized Views

GROUP BY Modifiers: ROLLUP, CUBE and TOTALS

Add subtotals and grand totals in ClickHouse with WITH ROLLUP, WITH CUBE and WITH TOTALS, plus GROUPING SETS, and learn the same logic with portable SQL.

A plain GROUP BY country, device returns one row per combination. Reports usually also want the subtotal per country and a grand total at the bottom. You could run three queries and glue them together, but ClickHouse can do it in a single pass with GROUP BY modifiers: WITH ROLLUP, WITH CUBE and WITH TOTALS. A related feature, GROUPING SETS, lets you list the exact groupings you want.

All ClickHouse statements below are read-only snippets, because the server cannot run on this site. The runnable editors reproduce the same results with portable SQL (UNION ALL), so you can see what the modifiers compute. Before this lesson you should know the basic aggregate functions.

The running example

Imagine a table of page views with a country, a device and a views count per row. We want totals at several levels:

Level Meaning
(country, device) the detailed rows
(country) subtotal for every country
() the grand total

WITH ROLLUP: hierarchical subtotals

WITH ROLLUP adds subtotals by dropping the grouping keys from right to left. With keys (country, device) you get the groupings (country, device), (country) and ():

SQL
SELECT country, device, sum(views) AS views
FROM page_views
GROUP BY country, device
WITH ROLLUP
ORDER BY country, device;

In the added rows the keys that were rolled up hold their default value: an empty string for String, 0 for numbers, 1970-01-01 for dates. That is different from most databases, which put NULL there. If your real data can contain empty strings or zeros, a subtotal row is hard to tell from a real one. Two ways around it:

  • Make the key Nullable and set group_by_use_nulls = 1, which makes rolled-up keys NULL.
  • Use the grouping() function, which returns a bitmask telling which keys were aggregated away in the current row (SQL standard syntax, available in recent versions).

The order of keys matters for ROLLUP. (device, country) would give subtotals per device instead.

WITH CUBE: every combination

WITH CUBE produces subtotals for all subsets of the keys, so with two keys you also get the (device) subtotal:

SQL
SELECT country, device, sum(views) AS views
FROM page_views
GROUP BY country, device
WITH CUBE;

The number of groupings is 2 to the power of the number of keys. Three keys give 8 groupings, five keys give 32. Cubes get expensive and produce lots of rows, so use them only on narrow dimension sets.

GROUPING SETS: choose exactly what you want

When neither shape fits, list the sets explicitly:

SQL
SELECT country, device, sum(views) AS views
FROM page_views
GROUP BY GROUPING SETS (
    (country, device),
    (device),
    ()
);

This is standard SQL syntax. It is the most flexible form: ROLLUP (a, b) is shorthand for GROUPING SETS ((a, b), (a), ()), and CUBE (a, b) for all four subsets. As with ROLLUP, rolled-up keys appear as defaults, so use grouping() if you must tell the rows apart.

WITH TOTALS: a separate totals row

WITH TOTALS is different. It does not add rows to the normal result. It computes one extra row, aggregated over all groups, and returns it as a separate block after the data:

SQL
SELECT country, sum(views) AS views, uniq(user_id) AS visitors
FROM page_views
GROUP BY country
WITH TOTALS
HAVING views > 100;

In clickhouse-client the totals appear as a second table beneath the result. In HTTP output with JSON format they appear in a totals field. This makes it easy for a dashboard to display a footer row without parsing mixed rows.

The setting totals_mode decides how HAVING interacts with the totals row. By default, the totals count every group, even those filtered out by HAVING, which is what you want for "share of the grand total" calculations. Other modes include only the groups that passed the filter, optionally using a threshold totals_auto_threshold (default 0.5).

Four ways to add totals

ROLLUP Subtotals right to left
CUBE Every key combination
GROUPING SETS You list the sets
TOTALS One extra summary row

Pick the smallest tool that fits.

1 / 2

Running the logic yourself

SQLite has no ROLLUP, but the same result is a union of groupings. This stand-in shows what ClickHouse computes. The label column marks subtotal rows explicitly instead of relying on default values.

SQL Stand-in: ROLLUP written as UNION ALL

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

Try changing the second branch to group by device to see what a CUBE adds. For TOTALS, the extra row is the last branch of the union, only returned separately.

SQL Stand-in: share of the grand total (what TOTALS enables)

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

Which one should you use?

Need Use
Subtotals along a hierarchy (year, month, day) WITH ROLLUP
A cross-tab with all subtotals WITH CUBE
Only a few specific groupings GROUPING SETS
A footer row for a dashboard WITH TOTALS
Tell subtotals from real data grouping() or group_by_use_nulls = 1

Remember that these modifiers cost extra memory and time proportional to the number of groupings. For repeated dashboards, precompute subtotals with a materialized view instead.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. With GROUP BY a, b WITH ROLLUP, which groupings are produced?

    ROLLUP removes keys from right to left, so the sets are (a, b), (a) and the empty grand total. CUBE would add (b).
  2. 2. What value do rolled-up key columns hold by default in ClickHouse?

    Unless group_by_use_nulls is enabled, keys that were aggregated away show type defaults, which can be confused with real data.
  3. 3. How does WITH TOTALS differ from WITH ROLLUP?

    WITH TOTALS computes a single row aggregated over all groups and delivers it separately, for example in the totals field of JSON output.
  4. 4. How many groupings does WITH CUBE produce for three keys?

    CUBE generates every subset of the keys: 2 to the power of 3 equals 8.

Summary

  • WITH ROLLUP adds hierarchical subtotals, dropping keys right to left.
  • WITH CUBE adds every key subset; its cost doubles with each key.
  • GROUPING SETS names the exact groupings you want.
  • WITH TOTALS adds one separate grand-total row; totals_mode controls how HAVING affects it.
  • Rolled-up keys hold type defaults unless you use grouping() or group_by_use_nulls.

Next: Materialized Views