v.18.12New Feature

New WITH ROLLUP Modifier for GROUP BY

Official CH changelog text
New WITH ROLLUP modifier for GROUP BY (alternative syntax: GROUP BY ROLLUP(...)). #2948
Introduces a new WITH ROLLUP modifier for GROUP BY clauses, allowing hierarchical aggregation of grouped data. An alternative syntax GROUP BY ROLLUP(...) is also supported.

Why it matters

This feature enables users to generate subtotals and grand totals in query results by performing hierarchical rollup aggregations automatically. It simplifies the process of obtaining aggregated summary data across multiple grouping levels without writing complex union queries.

How to use it

To use this feature, add the WITH ROLLUP modifier after the GROUP BY clause in your SQL query. Alternatively, you can use the syntax GROUP BY ROLLUP(column1, column2, ...). For example:

SELECT column1, column2,<br>       SUM(measure) FROM table<br>GROUP BY column1, column2 WITH ROLLUP

or
SELECT column1, column2,<br>       SUM(measure) FROM table<br>GROUP BY ROLLUP(column1, column2)

Related resources

PreviousNext