Snowflake SQL Tutorial 18 - ROLLUP, CUBE and GROUPING SETS in Snowflake

Snowflake SQL · Episode 18

In this post we are going to cover ROLLUP, CUBE and GROUPING SETS in Snowflake.

All three are extras we add to a GROUP BY. A plain GROUP BY gives us one row for every combination of values that exists in the table. These three ask Snowflake for subtotals and a grand total as well, in the same query and in one pass.

We will start with the plain GROUP BY we already know and add one clause at a time. Then we will come to the problem all three of them share. They write NULL into the columns a subtotal row is not about, and our data has NULLs in it too. The GROUPING function is how we tell those two kinds of blank apart.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 18 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start with a plain GROUP BY
3. Now let's add ROLLUP
4. Now let's try CUBE instead
5. Let's write the groupings out ourselves with GROUPING SETS
6. Now let's swap the two columns over
7. Let's look at two rows that read the same
8. Can we tell the two kinds of NULL apart?
9. Now let's put GROUPING to work
10. One last thing: the one we will actually write

Let's create some sample data

Every query in this post groups the same EMPLOYEES table on the same two columns, DEPT_ID and MANAGER_ID. The script below creates that table and puts eight rows into it.

Copy this into a Snowflake worksheet and run it once. Every post in this series uses the same table, so if you have run it already you are ready to go.

Sample data

-- Sample data for this post.
-- Run this once. Every post in this series uses the same EMPLOYEES table.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID          INT,
    FIRST_NAME      VARCHAR,
    LAST_NAME       VARCHAR,
    EMAIL           VARCHAR,
    DEPT_ID         INT,
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

INSERT INTO EMPLOYEES VALUES
    (1, 'Aisha',  'Khan',      'aisha.khan@example.com',      10,    6, 118000.00, NULL,  '2019-03-11'),
    (2, 'Marcus', 'Reid',      'marcus.reid@example.com',     10,    6,  96500.00, NULL,  '2021-07-01'),
    (3, 'Lena',   'Fischer',   'lena.fischer@example.com',    20,    6, 104000.00, 0.05,  '2018-01-22'),
    (4, 'Tomas',  'Novak',     'tomas.novak@example.com',     20,    3,  87250.00, NULL,  '2022-09-05'),
    (5, 'Priya',  'Raman',     'priya.raman@example.com',     30,    6,  96500.00, 0.10,  '2020-11-17'),
    (6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com',  10, NULL, 131000.00, NULL,  '2016-05-30'),
    (7, 'Sofia',  'Marino',    'sofia.marino@example.com',    30,    5,  71400.00, 0.08,  '2023-02-13'),
    (8, 'Grace',  'Lin',       'grace.lin@example.com',     NULL,    6,  92000.00, NULL,  '2021-04-19');

Two values in that script matter more than the rest, and they matter from the first query to the last. Daniel has no manager, so his MANAGER_ID is NULL. Grace has no department, so her DEPT_ID is NULL.

Both of those NULLs are real data. Keep them in mind. Every clause in this post adds rows that carry NULLs as well, and those ones are not data at all. Telling the two kinds apart is what this post builds up to.

One other table

One query near the end uses the ORDERS table instead. That is the other table from the same shared sample-data script, and the post on SUM, AVG, MIN and MAX lists it in full.

Let's start with a plain GROUP BY

Let's begin with the grouping we already know how to write. Department and manager, with a count.

Query

SELECT DEPT_ID, MANAGER_ID, COUNT(*) AS N FROM EMPLOYEES GROUP BY DEPT_ID, MANAGER_ID ORDER BY DEPT_ID, MANAGER_ID;

Result7 rows · 3 columns

DEPT_ID	MANAGER_ID	N
10	6	2
10	NULL	1
20	3	1
20	6	1
30	5	1
30	6	1
NULL	6	1

Seven rows came back. Every one of them is a real combination that exists in the table. As a count of who reports to whom, it is complete and correct.

As a report it is not what anybody asked for. Somebody reading this wants to know how many people are in department 10 altogether. They want to know how many people there are in total as well. Neither number is on the screen.

And no amount of GROUP BY on these two columns will put it there. GROUP BY gives us one row per combination, and a subtotal is not a combination.

We could get those numbers by writing the query three times. Once grouped by both columns, once by department on its own, once with no grouping at all. Then we stack the results with UNION ALL. That works, and it is what people did for years. The three clauses in this post do it in one pass.

Now let's add ROLLUP

Same query, same two columns. The only change is ROLLUP wrapped around them.

Query

SELECT DEPT_ID, MANAGER_ID, COUNT(*) AS N FROM EMPLOYEES GROUP BY ROLLUP(DEPT_ID, MANAGER_ID) ORDER BY DEPT_ID, MANAGER_ID;

Result12 rows · 3 columns

DEPT_ID	MANAGER_ID	N
10	6	2
10	NULL	1
10	NULL	3
20	3	1
20	6	1
20	NULL	2
30	5	1
30	6	1
30	NULL	2
NULL	6	1
NULL	NULL	1
NULL	NULL	8

Twelve rows now instead of seven. The seven we had before are all still there, and five new ones have joined them.

• 10 | NULL | 3 is the subtotal for department 10.
• 20 | NULL | 2 is the subtotal for department 20.
• 30 | NULL | 2 is the subtotal for department 30.
• NULL | NULL | 1 is the subtotal for the department that is NULL, which is Grace on her own.
• NULL | NULL | 8 is the grand total, everybody in the table.

That is four department subtotals and one grand total, added to the seven detail rows. Read one of them out loud and it is obvious what it means. Department 10, no particular manager, 3. That is everybody in department 10, whoever they report to.

Now look at which subtotals appeared. There is one for every DEPT_ID, and there is not one for any MANAGER_ID. That is not an accident, and it is not Snowflake choosing for us.

Order matters here

ROLLUP drops columns from the right of the list we give it. ROLLUP(DEPT_ID, MANAGER_ID) asks for department-and-manager, then department, then nothing. So the column order is part of the question we are asking. It is not a formatting choice. There is a section further down where we swap the two columns over and get a completely different report.

Now let's try CUBE instead

One word changes from the query above. ROLLUP becomes CUBE.

Query

SELECT DEPT_ID, MANAGER_ID, COUNT(*) AS N FROM EMPLOYEES GROUP BY CUBE(DEPT_ID, MANAGER_ID) ORDER BY DEPT_ID, MANAGER_ID;

Result16 rows · 3 columns

DEPT_ID	MANAGER_ID	N
10	6	2
10	NULL	1
10	NULL	3
20	3	1
20	6	1
20	NULL	2
30	5	1
30	6	1
30	NULL	2
NULL	3	1
NULL	5	1
NULL	6	1
NULL	6	5
NULL	NULL	1
NULL	NULL	8
NULL	NULL	1

Sixteen rows this time. CUBE does not roll up down one hierarchy. It takes every subset of the columns we give it.

So on top of the twelve rows ROLLUP produced, we also get a subtotal for each manager across all departments.

• NULL | 3 | 1 is everybody who reports to manager 3, whatever department they are in.
• NULL | 5 | 1 is the same for manager 5.
• NULL | 6 | 5 is the same for manager 6, who has the biggest team.
• NULL | NULL | 1 is the subtotal for the manager that is NULL, which is Daniel.

With two columns, CUBE asks for four groupings. With three columns it asks for eight. With four it asks for sixteen. It doubles every time we add a column, so it is worth knowing what we are ordering before we order it.

Which one do I want?

ROLLUP is for columns that form a hierarchy, where the order means something: country, then region, then city. A subtotal per region makes sense. A subtotal per city name across all countries does not.
CUBE is for columns that are independent and that you want to slice any way: product, channel, month.

Let's write the groupings out ourselves with GROUPING SETS

If neither of those is the shape we need, we can write the groupings out by hand. Here the request is a count by department, a count by manager, and the grand total. We do not want the combination of the two.

Query

SELECT DEPT_ID, MANAGER_ID, COUNT(*) AS N FROM EMPLOYEES GROUP BY GROUPING SETS ((DEPT_ID), (MANAGER_ID), ()) ORDER BY DEPT_ID, MANAGER_ID;

Result9 rows · 3 columns

DEPT_ID	MANAGER_ID	N
10	NULL	3
20	NULL	2
30	NULL	2
NULL	3	1
NULL	5	1
NULL	6	5
NULL	NULL	1
NULL	NULL	8
NULL	NULL	1

Nine rows. Four departments, four managers, one grand total. That is exactly what we asked for and nothing we did not.

None of the detail rows from the plain grouping are here at all. (DEPT_ID, MANAGER_ID) was never one of the sets, so Snowflake never produced them.

The empty parentheses () are the grand total. They are the grouping with no columns in it, which is one group holding every row. Leave them out and we get one row fewer.

This is also where the row counts come from

Once we read a clause as a list of groupings, all four row counts stop being mysterious. We count the groupings, count the rows each one produces, and add them up.

Clause	Groupings it asks for	Rows
GROUP BY A, B	(A,B)	7
GROUP BY ROLLUP(A, B)	(A,B), (A), ()	7 + 4 + 1 = 12
GROUP BY CUBE(A, B)	(A,B), (A), (B), ()	7 + 4 + 4 + 1 = 16
GROUP BY GROUPING SETS ((A),(B),())	(A), (B), ()	4 + 4 + 1 = 9

Same table, same two columns, four different row counts. Nothing changed except which set of groupings we asked for, and the row count follows from that.

Now let's swap the two columns over

This is the section that proves the point made earlier. We write ROLLUP(MANAGER_ID, DEPT_ID) instead of ROLLUP(DEPT_ID, MANAGER_ID). Nothing else is different.

Query

SELECT MANAGER_ID, DEPT_ID, COUNT(*) AS N
FROM EMPLOYEES
GROUP BY ROLLUP(MANAGER_ID, DEPT_ID)
ORDER BY MANAGER_ID, DEPT_ID;

Result12 rows · 3 columns

MANAGER_ID	DEPT_ID	N
3	20	1
3	NULL	1
5	30	1
5	NULL	1
6	10	2
6	20	1
6	30	1
6	NULL	1
6	NULL	5
NULL	10	1
NULL	NULL	1
NULL	NULL	8

Twelve rows again, but not the same twelve. The subtotals are per manager now instead of per department. 6 | NULL | 5 is all of manager 6's people across every department. In the earlier query the subtotal rows were 10 | NULL | 3, 20 | NULL | 2 and 30 | NULL | 2.

Same table, same two columns, same clause, same row count, completely different report. Put the hierarchy in the order it actually nests, country then region then city, and ROLLUP gives us the subtotals a human would expect.

Let's look at two rows that read the same

Now the part worth the price of admission. Go back to the ROLLUP result and look at these two rows.

DEPT_ID	MANAGER_ID	N
10	NULL	1
10	NULL	3

Both of them have nothing in the manager column. They mean completely different things.

The first one is a real group. Daniel is in department 10 and he has no manager, so his MANAGER_ID genuinely is NULL. That NULL came out of the table.

The second one is the subtotal for department 10. Its NULL is not data at all. It is ROLLUP telling us that this row is not about a particular manager, it is about all of them.

Watch out

We cannot tell those two apart by looking, and neither can anything downstream. Filter on MANAGER_ID IS NULL and we get both of them. Join another table on that column and we have a bug that will not announce itself.

And this is not a contrived example. It is the small employees table from the top of this page, and it happened because one person has no manager. Any real table with any nullable grouping column will do this to us.

The GROUPING SETS grid above is the same problem turned up a notch. It has three rows reading NULL | NULL. One is the department that is NULL, one is the manager that is NULL, and one is the grand total. Three rows, three different meanings, identical on screen.

Can we tell the two kinds of NULL apart?

We can. GROUPING(column) answers exactly that question.

Here is the same ROLLUP with two extra columns added, one per grouping column.

Query

SELECT DEPT_ID, MANAGER_ID, COUNT(*) AS N,
       GROUPING(DEPT_ID) AS G_DEPT, GROUPING(MANAGER_ID) AS G_MGR
FROM EMPLOYEES GROUP BY ROLLUP(DEPT_ID, MANAGER_ID) ORDER BY DEPT_ID, MANAGER_ID;

Result12 rows · 5 columns

DEPT_ID	MANAGER_ID	N	G_DEPT	G_MGR
10	6	2	0	0
10	NULL	1	0	0
10	NULL	3	0	1
20	3	1	0	0
20	6	1	0	0
20	NULL	2	0	1
30	5	1	0	0
30	6	1	0	0
30	NULL	2	0	1
NULL	6	1	0	0
NULL	NULL	1	0	1
NULL	NULL	8	1	1

The same twelve rows, two new columns, and the ambiguity is gone. Read the two rows from the previous section again.

• 10 | NULL | 1 | G_DEPT=0 | G_MGR=0 is the real NULL, straight from the data.
• 10 | NULL | 3 | G_DEPT=0 | G_MGR=1 is the stand-in that ROLLUP put there.

GROUPING(col) returns 1 when the NULL in that column is a stand-in the clause put there. It returns 0 when it is the actual value from the row. That is the whole function.

The bottom of the grid reads the same way. NULL | NULL | 1 | G_DEPT=0 | G_MGR=1 is the subtotal for the department that really is NULL. G_DEPT is 0, so that department value is real. NULL | NULL | 8 | G_DEPT=1 | G_MGR=1 is the grand total, with both columns flagged as stand-ins.

The whole function in two lines

1 means this NULL is a stand-in for a subtotal.
0 means this is real data.
That is the one sentence to take away from this episode.

Now let's put GROUPING to work

Once we have that number it is an ordinary column, and we can do ordinary things with it. The cheapest useful one needs nothing we have not already met. We sort by it.

Query

SELECT DEPT_ID, MANAGER_ID, COUNT(*) AS N,
       GROUPING(DEPT_ID) AS G_DEPT, GROUPING(MANAGER_ID) AS G_MGR
FROM EMPLOYEES
GROUP BY ROLLUP(DEPT_ID, MANAGER_ID)
ORDER BY GROUPING(DEPT_ID), DEPT_ID, GROUPING(MANAGER_ID), MANAGER_ID;

Result12 rows · 5 columns

DEPT_ID	MANAGER_ID	N	G_DEPT	G_MGR
10	6	2	0	0
10	NULL	1	0	0
10	NULL	3	0	1
20	3	1	0	0
20	6	1	0	0
20	NULL	2	0	1
30	5	1	0	0
30	6	1	0	0
30	NULL	2	0	1
NULL	6	1	0	0
NULL	NULL	1	0	1
NULL	NULL	8	1	1

Read the ORDER BY as real departments before the grand total, then inside a department, real managers before the subtotal. Within each department the detail rows come first and the subtotal follows them. The grand total is last. This is the version to reach for when a report simply needs to look right.

Or let's write a word where the blank was

The other thing people do with GROUPING is label the subtotal row instead of leaving it blank. That needs CASE, which is a Level 4 topic, so this is here for completeness rather than because we need it yet.

Query

SELECT CASE WHEN GROUPING(DEPT_ID) = 1 THEN 'ALL DEPARTMENTS'
            ELSE TO_VARCHAR(DEPT_ID) END AS DEPARTMENT,
       COUNT(*) AS N
FROM EMPLOYEES
GROUP BY ROLLUP(DEPT_ID)
ORDER BY GROUPING(DEPT_ID), DEPT_ID;

Result5 rows · 2 columns

DEPARTMENT	N
10	3
20	2
30	2
NULL	1
ALL DEPARTMENTS	8

The grand total now says what it is. TO_VARCHAR is in there because both branches of a CASE have to agree on a type, and DEPT_ID is a number.

Notice that the fourth row is still blank. That one is the real NULL department. GROUPING(DEPT_ID) is 0 for it, so CASE correctly left it alone. Telling the two kinds of blank apart is the entire point of the function.

One last thing: the one we will actually write

Everything above is the full picture. This is the shape that covers most real reporting. A breakdown with a total on the end, from ROLLUP on a single column.

Query

SELECT STATUS, COUNT(*) AS N, SUM(AMOUNT) AS TOTAL FROM ORDERS GROUP BY ROLLUP(STATUS) ORDER BY STATUS;

Result5 rows · 3 columns

STATUS	N	TOTAL
CANCELLED	2	3750.00
PENDING	3	7540.00
RETURNED	2	1830.00
SHIPPED	34	192307.00
NULL	41	205427.00

The four statuses, and then one more row with a blank STATUS. That row says 41 orders totalling 205427.00. It is the grand total, and it came out of one query rather than two queries and a UNION ALL. It is most of what anybody actually wants ROLLUP for.

If we ship this to a report, label that row with GROUPING(STATUS) rather than leaving it blank. A blank in a status column reads as missing data to everyone who did not write the query.

Key points

• ROLLUP(A, B) asks for the groupings (A,B), (A) and (). Subtotals down a hierarchy plus a grand total, in one pass.
• CUBE(A, B) asks for every subset of the columns, so we also get a subtotal per B across all values of A. The number of groupings doubles with each column we add.
• GROUPING SETS lets us write out exactly the groupings we want and nothing else. () is the grand total.
• The row count is not magic. It is the sum of the rows each grouping produces. Same table, same two columns: 7, 12, 16 and 9 rows.
• ROLLUP drops columns from the right, so the column order is part of the question. Swapping the two columns gives the same row count and a different report.
• All three clauses fill blank columns with NULL, and our data may contain NULLs too. Two rows can be identical on screen and mean completely different things.
• GROUPING(col) returns 1 when that NULL is a stand-in the clause added, and 0 when it is real data.
• GROUPING() is an ordinary expression. Use it in ORDER BY to push subtotals to the bottom, or in CASE to label them.
• ROLLUP on one column, a breakdown with a total on the end, is most of what anybody needs it for.

Next in this series

In the next post we will look at LISTAGG, which is the opposite of everything in this level. Instead of collapsing a group of rows into a number, LISTAGG collapses it into a string.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the script above. The EMPLOYEES and ORDERS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Clause and function behaviour is described from the Snowflake GROUP BY documentation and the GROUPING function documentation.