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 O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.