thinQmaster Virtual testing partner Adaptive practice exams that find your weak spots and drill them. Every answer explained. From $69
Enroll

thinQmaster

Find your exam

Five questions free, before you decide.

Step 1 · choose a vendor

In one of our camps? Your practice exams come with the tuition — they are on your study page already.

Data & Analytics · Advanced

ROLLUP and CUBE

GROUP BY extensions that add subtotal rows: ROLLUP for hierarchical subtotals and CUBE for every combination of the grouping columns.

Definition

ROLLUP(Year, Quarter) yields totals by Year and Quarter, by Year, and a grand total. CUBE(Region, Product) yields every combination, including totals by Region alone, by Product alone and overall. Subtotal rows show NULL in the rolled-up columns; the GROUPING() function tells them apart from real NULLs.

Example

SELECT Region, Product, SUM(Sales) FROM Sales GROUP BY CUBE(Region, Product);

Exam tip

With n columns, ROLLUP produces n+1 grouping levels while CUBE produces 2^n.

Related terms