What is the difference between cube, rollup and groupBy operators?

sql

data-aggregation

sql-operators

database-performance

byAnton Shumikhin·Jan 10, 2025

Differences between CUBE, ROLLUP, and GROUP BY boil down to the level of data summarization:

GROUP BY: Groups rows that share a property so it can aggregate their data.

SELECT Country, COUNT(User) 
-- Classic group by, as 'vanilla' as my ain't database
FROM Users GROUP BY Country;

ROLLUP: Does hierarchical totaling of data from the most granular to the least.

SELECT Country, City, COUNT(User) 
-- Let's 'rollup' like a senior database
FROM Users GROUP BY ROLLUP(Country, City);

CUBE: Provides aggregates for all combinations of group columns, paves the way for full multidimensional analysis.

SELECT Country, City, COUNT(User)
-- 'CUBE', because why not see EVERYTHING at once?
FROM Users GROUP BY CUBE(Country, City);

ROLLUP results in linear, hierarchical subtotals. CUBE yields a comprehensive set of subtotals, perfect for multidimensional queries. GROUP BY collates and summarizes data without any hierarchy.

An analytical dive into groupings

Embracing nulls with CUBE

In CUBE, it’s all about comprehensive data aggregation. Resultant null values represent 'missing' data groups, which are partial group combinations. This all-encompassing approach can lead to an extensive number of rows in the result set, especially when dealing with large datasets.

Hierarchical magic of ROLLUP

ROLLUP is about gradual data aggregation, moving from detailed data points to broader totals. This left-to-right synthesis aligns with typical data analysis flow: you start with specific data and gradually rollup to general insights. Remember, in ROLLUP, the sequence of columns affects the hierarchy.

Going classic with GROUP BY

GROUP BY is your go-to SQL tool for straightforward data consolidation. It guarantees that each row is part of exactly one summarized group. It focuses on pure aggregation, foregoing the hierarchical structure of ROLLUP or CUBE.

Practical aspects of SQL operators

CUBE and ROLLUP in a DBA's utility belt

CUBE and ROLLUP are perfect for data warehousing where multidimensional analysis is a daily requirement. These operators provide a detailed view without multiple queries while sacrificing simplicity for power — get ready to deal with more null values with CUBE.

Decoding aggregates with GROUPING functions

Understand your aggregate better with the GROUPING or GROUPING_ID functions. These can help distinguish between rows created by CUBE or ROLLUP from standard groupings.

Performance penalties of CUBE

CUBE can be resource-intensive due to the large number of output rows. As such, ROLLUP is generally more efficient for performance, provided your data can be structured into a hierarchy.

Custom group control: GROUPING SETS

When CUBE and ROLLUP don't give you the perfect grouping, GROUPING SETS is your knight-in-shining-armor. It lets you define exactly which groupings to include, giving you the same flexibility as CUBE without sacrificing the specificity of GROUP BY.

explain-codes / Sql / What is the difference between cube, rollup and groupBy operators?

Linked

Add a summary row with totals



Create a SQL query to retrieve most recent records



How to include "zero" / "0" results in COUNT aggregate?



When is a good situation to use a full outer join?



Using group by on multiple columns

