For AI agents: the complete documentation index is at llms.txt. Every page is also available as markdown by appending .md to its URL, or by sending an Accept: text/markdown request header.

GROUPING SETS, ROLLUP, and CUBE

GROUPING SETS, ROLLUP, and CUBE compute several levels of aggregation in one query, such as detail rows, subtotals, and a grand total. QuestDB computes every level in a single scan of the data, instead of running a separate GROUP BY per level and combining the results with UNION ALL.

Syntax​

Grouping sets can be used with both GROUP BY and SAMPLE BY.

GROUP BY with ROLLUP or CUBE
SELECT column [, ...], aggregate(column) [, ...]
FROM table
[WHERE condition]
GROUP BY [column [, ...],] { ROLLUP | CUBE } (column [, ...])
GROUP BY with GROUPING SETS
SELECT column [, ...], aggregate(column) [, ...]
FROM table
[WHERE condition]
GROUP BY GROUPING SETS ({ (column [, ...]) | () } [, ...])
SAMPLE BY
SELECT timestamp, [column [, ...],] aggregate(column) [, ...]
FROM table
[WHERE condition]
SAMPLE BY [count]unit
    { ROLLUP(column [, ...])
    | CUBE(column [, ...])
    | GROUPING SETS ({ (column [, ...]) | () } [, ...]) }
    [FROM timestamp TO timestamp]
    [FILL(...)]
    [ALIGN TO CALENDAR [TIME ZONE tz] [WITH OFFSET offset]]

With GROUP BY, plain columns can precede ROLLUP or CUBE, as described in Composite syntax.

With SAMPLE BY, the grouping clause comes directly after the sample interval, before FROM ... TO and FILL. See SAMPLE BY integration for the rules that differ from a regular SAMPLE BY query.

GROUPING SETS​

GROUPING SETS gives explicit control over which grouping combinations to compute. Each set in the list produces its own group of aggregated rows.

Explicit grouping setsDemo this query
SELECT symbol, side, SUM(amount) AS total_amount, COUNT(*) AS trade_count
FROM trades
WHERE timestamp IN '$now-1m..$now'
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
GROUP BY GROUPING SETS (
    (symbol, side),
    (symbol),
    ()
);
  • (symbol, side) groups by both columns (detail rows)
  • (symbol) groups by symbol only (subtotals per symbol, side is NULL)
  • () is the empty set, producing a single grand total row (both columns NULL)

The grand total row is returned even when no rows match the WHERE clause. In that case COUNT(*) returns 0 and SUM() returns NULL.

You can specify any combination of column subsets. ROLLUP and CUBE are shorthand for common GROUPING SETS patterns.

The list must contain at least one set, with no trailing comma. GROUPING SETS () is a syntax error; for a grand total only, write GROUPING SETS (()).

Every set is written in parentheses, including one-column sets: GROUPING SETS ((symbol), (side), ()), not GROUPING SETS (symbol, side, ()).

QuestDB does not remove duplicate grouping sets. Listing the same set twice, as in GROUPING SETS ((symbol), (symbol)), returns its rows twice.

ROLLUP​

ROLLUP generates hierarchical subtotals, progressively dropping columns from right to left. With N columns, ROLLUP produces N+1 grouping sets.

Trade volume breakdown with ROLLUPDemo this query
SELECT symbol, side,
       SUM(price * amount) AS volume,
       COUNT(*) AS trades
FROM trades
WHERE timestamp IN '$now-1m..$now'
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
GROUP BY ROLLUP(symbol, side)
ORDER BY symbol, side;

This produces:

  • Per-symbol, per-side detail rows
  • Per-symbol subtotals (side is NULL)
  • A single grand total row (both NULL)

ROLLUP(symbol, side) is equivalent to:

GROUP BY GROUPING SETS (
    (symbol, side),
    (symbol),
    ()
)

With three columns, ROLLUP(a, b, c) produces four grouping sets:

GROUP BY GROUPING SETS (
    (a, b, c),
    (a, b),
    (a),
    ()
)

CUBE​

CUBE generates all possible combinations of the specified columns. With N columns, CUBE produces 2^N grouping sets.

Cross-tabulation with CUBEDemo this query
SELECT symbol, side,
       SUM(amount) AS total_amount,
       GROUPING_ID(symbol, side) AS grp
FROM trades
WHERE timestamp IN '$now-1m..$now'
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
GROUP BY CUBE(symbol, side)
ORDER BY grp, symbol, side;

CUBE(symbol, side) is equivalent to:

GROUP BY GROUPING SETS (
    (symbol, side),   -- both grouped
    (symbol),         -- symbol only
    (side),           -- side only
    ()                -- grand total
)

Ordering by GROUPING_ID groups the output by aggregation level:

  • grp=0: all detail combinations
  • grp=1: per-symbol totals (side rolled up)
  • grp=2: per-side totals (symbol rolled up)
  • grp=3: grand total

CUBE accepts at most 15 columns. The number of grouping sets is also capped by cairo.sql.max.grouping.sets, which defaults to 4096. With the default, CUBE accepts at most 12 columns (2^12 = 4,096 sets). Using 13 to 15 columns requires raising that property.

Composite syntax​

Plain GROUP BY columns can be combined with ROLLUP or CUBE. The plain columns are always included in every grouping set.

Symbol always grouped, side rolled upDemo this query
SELECT symbol, side, SUM(amount) AS total_amount
FROM trades
WHERE timestamp IN '$now-1m..$now'
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
GROUP BY symbol, ROLLUP(side);

This is equivalent to:

GROUP BY GROUPING SETS (
    (symbol, side),
    (symbol)
)

There is no empty set () here because symbol is always present.

Plain columns must come before ROLLUP or CUBE. The following forms are rejected:

  • Columns after the construct: GROUP BY ROLLUP(side), symbol
  • More than one construct: GROUP BY ROLLUP(symbol), CUBE(side)
  • Plain columns with GROUPING SETS: GROUP BY symbol, GROUPING SETS (...)

For any of these, write out the sets you need with GROUPING SETS. For example, GROUP BY symbol, GROUPING SETS ((side), ()) is written as GROUP BY GROUPING SETS ((symbol, side), (symbol)).

Expressions as keys​

ROLLUP, CUBE, and GROUPING SETS accept column references only. An expression written inside them, such as ROLLUP(a + b), is rejected even when every set contains it. A SELECT alias of an expression is accepted only when every grouping set contains it. Plain keys before ROLLUP or CUBE in composite syntax can be expressions.

To roll up an expression, compute it in a subquery and group by its output column:

Roll up an expression through a subqueryDemo this query
SELECT symbol, price_level, SUM(amount) AS total_amount
FROM (
    SELECT symbol, round(price, -2) AS price_level, amount
    FROM trades
    WHERE timestamp IN '$now-1h..$now'
      AND symbol = 'BTC-USDT'
)
GROUP BY ROLLUP(symbol, price_level);

GROUPING() and GROUPING_ID() functions​

When columns are rolled up, they appear as NULL in the result. The data might also contain genuine NULL values. GROUPING() and GROUPING_ID() distinguish between the two.

GROUPING(column)​

Accepts a single column. Returns:

  • 0 if the column is actively grouped (a NULL is a real data value)
  • 1 if the column is rolled up (the NULL is a placeholder)
Identify rolled-up rowsDemo this query
SELECT symbol, side, SUM(amount) AS total_amount,
       GROUPING(symbol) AS gs,
       GROUPING(side) AS gsd
FROM trades
WHERE timestamp IN '$now-1m..$now'
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
GROUP BY ROLLUP(symbol, side)
ORDER BY gs, gsd, symbol, side;

In the results:

gsgsdMeaning
00Detail row: both columns actively grouped
01Subtotal: grouped by symbol, side rolled up
11Grand total: both columns rolled up

GROUPING_ID(column1, column2, ...)​

Accepts one to 31 columns. Returns an integer bitmask combining the GROUPING() values of all specified columns. Bit positions are assigned right-to-left: the rightmost argument occupies bit 0 (least significant bit).

Bitmask for aggregation levelsDemo this query
SELECT symbol, side, SUM(amount) AS total_amount,
       GROUPING_ID(symbol, side) AS grp
FROM trades
WHERE timestamp IN '$now-1m..$now'
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
GROUP BY CUBE(symbol, side)
ORDER BY grp, symbol, side;

For GROUPING_ID(symbol, side), bit 1 is assigned to symbol and bit 0 to side:

grpBinaryMeaning
00b00Both columns grouped
10b01side rolled up
20b10symbol rolled up
30b11Both rolled up (grand total)

Writing GROUPING_ID(side, symbol) would reverse the bit assignments.

SAMPLE BY integration​

Grouping sets work with QuestDB's SAMPLE BY clause for time-bucketed aggregation with multiple rollup levels.

Hourly breakdown with ROLLUPDemo this query
SELECT timestamp, symbol, SUM(amount) AS total_amount, AVG(price) AS avg_price
FROM trades
WHERE timestamp IN '$now-1d..$now'
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
SAMPLE BY 1h ROLLUP(symbol)
ORDER BY timestamp, symbol;

Each time bucket contains one row per symbol plus one grand total row (where symbol is NULL). The timestamp column is never rolled up. It is always present as the time bucket key. A bucket with no input rows produces no rows, including no grand total row. FILL can add rows for such buckets, as described in FILL support.

In a regular SAMPLE BY query, every non-aggregated column in the SELECT list becomes a key. With grouping sets, this does not happen: every non-aggregated column other than the timestamp must appear in the ROLLUP, CUBE, or GROUPING SETS clause. SAMPLE BY also does not support composite syntax. To keep a column grouped at every level, list it in each set:

Symbol in every set, side rolled upDemo this query
SELECT timestamp, symbol, side, SUM(amount) AS total_amount
FROM trades
WHERE timestamp IN '$now-1d..$now'
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
SAMPLE BY 1h GROUPING SETS ((symbol, side), (symbol))
ORDER BY timestamp, symbol, side;

The input can be a table, or a subquery or CTE that keeps the designated timestamp:

ROLLUP over a CTEDemo this query
WITH btc AS (
    SELECT * FROM trades
    WHERE timestamp IN '$now-1d..$now'
      AND symbol = 'BTC-USDT'
)
SELECT timestamp, side, SUM(amount) AS total_amount
FROM btc
SAMPLE BY 1h ROLLUP(side)
ORDER BY timestamp, side;

ALIGN TO FIRST OBSERVATION is not supported with grouping sets. The sample interval must be a constant, so SAMPLE BY $1 h is rejected. The FROM bound of FROM ... TO must be a timestamp literal; functions, expressions, and bind variables are not supported there.

FILL support​

FILL works with grouping sets. Missing time buckets are filled per key combination: each distinct (symbol, grouping level) pair gets its own fill row.

FILL fills the key combinations that the input produced. One matching row is enough to fill its series, including the grand total, across the whole range. When no input rows match, there is nothing to fill and the query returns no rows, even with FROM ... TO and even for the grand total of ROLLUP or CUBE. Plain keyed SAMPLE BY ... FILL behaves the same way. This differs from GROUP BY, where the empty set () returns a grand total row on empty input.

SAMPLE BY with FILL and ROLLUPDemo this query
SELECT timestamp, symbol, SUM(amount) AS total_amount, AVG(price) AS avg_price
FROM trades
WHERE timestamp IN '$now-1d..$now'
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
SAMPLE BY 1h ROLLUP(symbol) FILL(0)
ORDER BY timestamp, symbol;

Supported FILL modes:

FILL modeSupported
FILL(NONE)Yes
FILL(NULL)Yes
FILL(value)Yes
FILL(PREV)No
FILL(LINEAR)No

FILL(NULL) and FILL(value) require distinct grouping sets. Queries that repeat a set, such as GROUPING SETS ((symbol), (symbol)) or ROLLUP(symbol, symbol), are rejected. Two sets count as duplicates when they contain the same columns, in any order or through an alias, so GROUPING SETS ((symbol, side), (side, symbol)) is rejected too. FILL(NONE) accepts duplicate sets.

FILL(NULL) and FILL(value) also support at most 31 rolled-up keys. A key is rolled up when at least one grouping set leaves it out, so ROLLUP(a, b, c) rolls up all three keys. A plain key before ROLLUP or CUBE in composite syntax is in every set and does not count.

GROUPING() and GROUPING_ID() values are preserved in fill rows. They are not replaced by the fill value.

GROUPING values preserved in fill rowsDemo this query
SELECT GROUPING(symbol) AS gs, timestamp, symbol, SUM(amount) AS total_amount
FROM trades
WHERE timestamp IN '$now-1d..$now'
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
SAMPLE BY 1h ROLLUP(symbol) FILL(NULL)
ORDER BY timestamp, gs, symbol;

A fill row for a missing hour shows gs=0 for detail-level fills and gs=1 for grand-total-level fills, just like real data rows. Only aggregate columns get the fill value.

Execution​

QuestDB computes all grouping sets in a single scan of the input. When parallel GROUP BY is enabled, the query runs on multiple worker threads, and EXPLAIN shows an Async Group By node with groupingSets: true, or Async JIT Group By when the WHERE filter is JIT-compiled. Queries with many grouping sets relative to the number of query workers run on a single thread.

Grouping sets aggregation is never vectorized. When you only need one level of aggregation, a plain GROUP BY over the same keys can be faster.

When each grouping set on its own would run as a vectorized GROUP BY, typically a single key with simple aggregates such as sum(), a UNION ALL of one plain GROUP BY per set can be several times faster with parallel execution. Run EXPLAIN on the plain GROUP BY: vectorized: true means it takes the faster path. With two or more keys the difference is much smaller.

ROLLUP(symbol)Demo this query
SELECT symbol, SUM(amount) AS total_amount
FROM trades
WHERE timestamp IN '$now-1d..$now'
GROUP BY ROLLUP(symbol);
The same rows as UNION ALLDemo this query
SELECT symbol, SUM(amount) AS total_amount
FROM trades
WHERE timestamp IN '$now-1d..$now'
GROUP BY symbol
UNION ALL
SELECT NULL::SYMBOL, SUM(amount)
FROM trades
WHERE timestamp IN '$now-1d..$now';

Rows are not returned in any guaranteed order. Use ORDER BY when order matters.

Limitations​

  • Expressions not allowed in ROLLUP, CUBE, or GROUPING SETS. Compute them in a subquery instead. See Expressions as keys.

  • No mixed qualified/unqualified references to the same column. ROLLUP(a, t.a) is rejected. Use one form consistently.

  • Parentheses required around each set in GROUPING SETS. Write (symbol), not symbol.

  • One ROLLUP or CUBE per GROUP BY, placed last. GROUP BY ROLLUP(a), CUBE(b) and GROUP BY ROLLUP(a), b are rejected, and GROUPING SETS cannot be combined with plain columns. See Composite syntax.

  • Not supported with LATEST ON. Rejected with an error.

  • Not supported with HORIZON JOIN. Rejected with an error.

  • Not supported in correlated LATERAL subqueries. A LATERAL subquery that references the outer table cannot use grouping sets. Uncorrelated LATERAL subqueries can.

  • Not supported in materialized views. A materialized view query cannot use GROUPING SETS, ROLLUP, or CUBE.

  • FILL(PREV) and FILL(LINEAR) not supported with grouping sets.

  • ALIGN TO FIRST OBSERVATION not supported with grouping sets in SAMPLE BY.

  • SAMPLE BY interval must be a constant. A bind variable such as SAMPLE BY $1 h is rejected with grouping sets.

  • FILL(NULL) and FILL(value) require distinct grouping sets and support at most 31 rolled-up keys in SAMPLE BY. See FILL support.

  • SAMPLE BY ... FILL returns no rows on empty input, not even a grand total, because there is no key combination to fill. See FILL support.

  • SAMPLE BY ... FROM requires a timestamp literal. Functions, expressions, and bind variables in the FROM bound are not supported with grouping sets.

  • GROUPING_ID() accepts at most 31 arguments, because its result is an INT bitmask. GROUPING() and the number of GROUP BY keys have no such limit.

  • Maximum grouping sets per query, controlled by the cairo.sql.max.grouping.sets configuration property (default 4096). ROLLUP produces N+1 sets, CUBE produces 2^N sets, and explicit GROUPING SETS produces one set per listed group. Queries exceeding this limit are rejected at parse time. CUBE also has a hard limit of 15 columns, so with the default it accepts at most 12.

See also​