B.7. ROLLUP AND CUBE

ROLLUP and CUBE are used with GROUP BY to find additional sums or aggregates. ROLLUP adds summary rows from right to left on the GROUP BY list. For example,

GROUP BY a, b, c WITH ROLLUP

yields summaries for: ( a, b ) and ( c ) values.

B.7.1. ROLLUP

Note: Distinct aggregates, for example, AVG(DISTINCT column_name) and COUNT(DISTINCT column_name), are not supported when using CUBE or ROLLUP. GROUP BY a, b with ROLLUP

ROLLUP operator

provides summary rows above those from GROUP BY

The GROUP BY column order specifies a ROLLUP hierarchical order, from hi (left) to lo (right).

Groups are summarized in this hierarchical order, from the lowest level to the highest.

Example: GROUP BY a, b, c WITH ROLLUP

Lists values for a, b, c, ...

Get Transact-SQL Desk Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.