After learning about grouping sets, something else caught my eye: Cube and Rollup. While everything you can do using cube and rollup can also be achieved with Grouping Sets, these two are often more convenient for specific scenarios. One of the best use cases for Rollup is when you need reports with daily, monthly, and overall summaries of data.
This article highlights the differences between Cube and Rollup and when to use them. It explains both concepts in terms of Grouping Sets, so make sure you’re familiar with that first. If not, start here — PostgreSQL — Grouping Sets.
Preparing Dataset
For this article, I’m using an employees dataset with ~100K records. The table structure is as shown below.
You can create a similar dataset by following this article. The only changes are replacing the age column with the age_category values and having fewer values for the department. The age_category column contains random values from the list:
['<18', '18-30', '>30']Cube
The CUBE(x, y, ...) operation generates grouping sets for all possible combinations of the specified columns, including individual columns, their combinations, and the overall total. It’s a powerful tool for multi-dimensional analysis, as it provides every level of aggregation in a single query. In terms of Grouping Sets, CUBE(x, y, z) is equivalent to grouping sets((), (x), (y), (z), (x, y), (x, z), (y, z), (x, y, z)).
Get Nishaanth S P’s stories in your inbox
Join Medium for free to get updates from this writer.
Here is an example of a query with Cube:
postgres=# SELECT COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
department,
age_category
FROM employees
GROUP BY CUBE (department, age_category);
employee_count | total_salary | department | age_category
----------------+--------------+------------+--------------
100000 | 10521132786 | |
8282 | 876406498 | BIO | >30
8481 | 887840866 | EEE | >30
8318 | 878340577 | EEE | 18-30
8354 | 878803885 | CSE | <18
8403 | 891268155 | MECH | 18-30
8229 | 858600428 | CSE | >30
8401 | 877199335 | BIO | <18
8216 | 862884169 | CSE | 18-30
8224 | 860332851 | EEE | <18
8355 | 881509121 | BIO | 18-30
8313 | 880791187 | MECH | >30
8424 | 887155714 | MECH | <18
24799 | 2600288482 | CSE |
25038 | 2635114954 | BIO |
25023 | 2626514294 | EEE |
25140 | 2659215056 | MECH |
33305 | 3503638979 | | >30
33292 | 3514002022 | | 18-30
33403 | 3503491785 | | <18
(20 rows)The equivalent of the same with Grouping sets is as follows:
postgres=# SELECT COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
department,
age_category
FROM employees
GROUP BY GROUPING SETS ((), (department), (age_category), (department, age_category));
employee_count | total_salary | department | age_category
----------------+--------------+------------+--------------
100000 | 10521132786 | |
8282 | 876406498 | BIO | >30
8481 | 887840866 | EEE | >30
8318 | 878340577 | EEE | 18-30
8354 | 878803885 | CSE | <18
8403 | 891268155 | MECH | 18-30
8229 | 858600428 | CSE | >30
8401 | 877199335 | BIO | <18
8216 | 862884169 | CSE | 18-30
8224 | 860332851 | EEE | <18
8355 | 881509121 | BIO | 18-30
8313 | 880791187 | MECH | >30
8424 | 887155714 | MECH | <18
24799 | 2600288482 | CSE |
25038 | 2635114954 | BIO |
25023 | 2626514294 | EEE |
25140 | 2659215056 | MECH |
33305 | 3503638979 | | >30
33292 | 3514002022 | | 18-30
33403 | 3503491785 | | <18
(20 rows)Rollup
The ROLLUP(x, y, ...) operation generates grouping sets for hierarchical aggregations, starting from the most detailed level and rolling up to the total. It provides a way to perform multi-level aggregation in a single query, where each successive level is a superset of the previous one. In terms of Grouping Sets, ROLLUP(x, y, z) is equivalent to grouping sets((), (x), (x, y), (x, y, z)).
Here is an example of a query with Rollup:
postgres=# SELECT COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
department,
age_category
FROM employees
GROUP BY ROLLUP (department, age_category);
employee_count | total_salary | department | age_category
----------------+--------------+------------+--------------
100000 | 10521132786 | |
8282 | 876406498 | BIO | >30
8481 | 887840866 | EEE | >30
8318 | 878340577 | EEE | 18-30
8354 | 878803885 | CSE | <18
8403 | 891268155 | MECH | 18-30
8229 | 858600428 | CSE | >30
8401 | 877199335 | BIO | <18
8216 | 862884169 | CSE | 18-30
8224 | 860332851 | EEE | <18
8355 | 881509121 | BIO | 18-30
8313 | 880791187 | MECH | >30
8424 | 887155714 | MECH | <18
24799 | 2600288482 | CSE |
25038 | 2635114954 | BIO |
25023 | 2626514294 | EEE |
25140 | 2659215056 | MECH |
(17 rows)The equivalent of the same with Grouping sets is as follows:
postgres=# SELECT COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
department,
age_category
FROM employees
GROUP BY GROUPING SETS ((), (department), (department, age_category));
employee_count | total_salary | department | age_category
----------------+--------------+------------+--------------
100000 | 10521132786 | |
8282 | 876406498 | BIO | >30
8481 | 887840866 | EEE | >30
8318 | 878340577 | EEE | 18-30
8354 | 878803885 | CSE | <18
8403 | 891268155 | MECH | 18-30
8229 | 858600428 | CSE | >30
8401 | 877199335 | BIO | <18
8216 | 862884169 | CSE | 18-30
8224 | 860332851 | EEE | <18
8355 | 881509121 | BIO | 18-30
8313 | 880791187 | MECH | >30
8424 | 887155714 | MECH | <18
24799 | 2600288482 | CSE |
25038 | 2635114954 | BIO |
25023 | 2626514294 | EEE |
25140 | 2659215056 | MECH |
(17 rows)In short, use ROLLUP for hierarchical summaries and CUBE for exhaustive, all-encompassing aggregations.
Conclusion
So, next time you find yourself drowning in repetitive queries for every aggregation combination, just remember: GROUPING SETS, CUBE, and ROLLUP are like the cheat codes of your SQL game. While GROUPING SETS is the all-rounder, CUBE and ROLLUP are the power-ups that make your reports quicker and more efficient.
No more writing countless queries — just let these functions do the heavy lifting. Seriously, where was this magic when we first started?
Happy querying!
