Snowflake SQL Tutorial 16 - GROUP BY ALL in Snowflake

Snowflake SQL · Episode 16

In this post we are going to cover GROUP BY ALL in Snowflake, along with two other shortcuts that do a similar job: the ordinal and the column alias.

They all solve the same annoyance. When we group by an expression, the GROUP BY clause makes us write that expression a second time, and then both copies have to stay identical for ever. We saw that at the end of the previous post.

We will write the long version first, so the problem is on the page. Then we will take the three shortcuts one at a time, worst first, and look at what each one gets wrong as well as what it gets right.

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

Episode 16 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 the expression written out in full
3. Now let's try the first shortcut: GROUP BY 1
4. Now let's group by the column alias instead
5. Now let's try GROUP BY ALL
6. Can we compare all four spellings?
7. Now let's group rows into bands
8. One last thing: which one to use

Let's create some sample data

Most of this page runs against EMPLOYEES, with a few queries on ORDERS from the same shared sample-data script. The script below creates EMPLOYEES and fills it.

Copy this into a Snowflake worksheet and run it once. Then you can run every query on this page yourself.

Sample data — run once

-- Creates the EMPLOYEES table most of this page runs against.
-- Part of the shared sample-data script for the series; run it once.
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');

The ORDERS half of the script is printed in full in the post on SUM and AVG, so it is not repeated here.

Let's start with the expression written out in full

Here is the problem this post solves. When we group by an expression rather than a plain column, we have to write that expression out in the GROUP BY clause as well as in the SELECT list.

Let's group the employees by how long their first name is.

The portable spelling — the expression written out in full

SELECT LENGTH(FIRST_NAME) AS NAME_LENGTH, COUNT(*) AS N FROM EMPLOYEES GROUP BY LENGTH(FIRST_NAME) ORDER BY NAME_LENGTH;

Result3 rows · 2 columns

NAME_LENGTH	N
4	1
5	5
6	2

Three groups came back: one name of four letters, five names of five, and two names of six. Those counts add up to the eight rows in the table, which is the quickest check that a grouping has not quietly lost anybody.

Nothing is wrong with that query. It runs anywhere, on any database, which is exactly why it is worth having as our baseline.

But read it as a piece of writing. LENGTH(FIRST_NAME) appears twice, once to make the column and once to define the groups, and those two copies have to stay identical for as long as the query exists.

You have met this expression before

Episode 7 sorted these same people with ORDER BY LENGTH(FIRST_NAME) and walked through the one four-letter name, the five five-letter names and the two six-letter ones. Same expression, different clause. There it decided the order. Here it decides the groups.

Snowflake offers three shorter spellings. All three produce identical results, and all three are below with their real output. We will take them worst first.

Now let's try the first shortcut: GROUP BY 1

The first shortcut refers to the column by its position in the SELECT list. DEPT_ID is the first item, so GROUP BY 1 groups by it.

Query

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

Result4 rows · 2 columns

DEPT_ID	N
10	3
20	2
30	2
NULL	1

The same four groups the previous post produced from GROUP BY DEPT_ID, written with one character instead of a column name. ORDER BY takes an ordinal too, which is why this query has a 1 doing two different jobs.

Where it genuinely earns its keep is on an expression, because then the character it replaces is a whole function call. Let's count the orders by status, uppercased.

The ordinal standing in for an expression

SELECT UPPER(STATUS) AS STATUS_UPPER, COUNT(*) AS N FROM ORDERS GROUP BY 1 ORDER BY 1;

Result4 rows · 2 columns

STATUS_UPPER	N
CANCELLED	2
PENDING	3
RETURNED	2
SHIPPED	34

Four statuses, uppercased and counted, and UPPER(STATUS) is written exactly once. For a throwaway query in a worksheet this is completely fine.

Where it bites

The number is a position, not a name. Add a column to the front of the SELECT list and GROUP BY 1 silently starts grouping by something else. Nothing errors. The result is just quietly wrong.
This shortcut has form, and you have already watched it misbehave. Episode 7 showed ORDER BY 2, 3 DESC moving to different columns when the select list changed. It is worse here, because a shifted ORDER BY reorders your output while a shifted GROUP BY changes what the numbers mean.

Now let's group by the column alias instead

The second shortcut is the one worth writing in anything another person has to read. Give the expression an alias in the SELECT list, then group by that alias.

Query

SELECT UPPER(STATUS) AS STATUS_UPPER, COUNT(*) AS N FROM ORDERS GROUP BY STATUS_UPPER ORDER BY STATUS_UPPER;

Result4 rows · 2 columns

STATUS_UPPER	N
CANCELLED	2
PENDING	3
RETURNED	2
SHIPPED	34

The same four rows as the ordinal version above, from a query that names what it is grouping by. The expression exists in exactly one place, and no amount of reordering the SELECT list can make the clause point at a different column.

Now let's try it on a long expression

The longer the expression, the more the alias is worth. Here is the same monthly grouping we finished the previous post with, written so that DATE_TRUNC appears once. This version asks for the order count only.

Query

SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH, COUNT(*) AS ORDERS FROM ORDERS GROUP BY MONTH ORDER BY MONTH LIMIT 6;

Result6 rows · 2 columns

MONTH	ORDERS
2023-01-01	2
2023-02-01	2
2023-03-01	2
2023-04-01	2
2023-05-01	2
2023-06-01	2

One row per month with the order count, and GROUP BY MONTH rather than a second copy of the DATE_TRUNC call. The query ends in LIMIT 6, so the grid stops at six months.

If the report ever has to become quarterly, there is one line to change instead of two lines to change in step.

This is a Snowflake feature, not standard SQL

If you have used other databases, that query may have just surprised you. In standard SQL the GROUP BY clause is evaluated before the SELECT list, so the alias does not exist yet and most engines reject it. Snowflake resolves the name early, which is the same generosity episode 10 found with a column alias in a WHERE clause. The documentation sentence quoted there lists GROUP BY explicitly. Readable, stable, and not portable.

Now let's try GROUP BY ALL

The third one is the most Snowflake-specific of the three, and once you have used it you will miss it everywhere else. Not a column, not a number. Just the word ALL.

What it means is this. Look at the SELECT list, work out which items are not aggregates, and group by those. In the query below DEPT_ID is not an aggregate and COUNT and AVG are, so the grouping key is DEPT_ID and nothing else.

Query

SELECT DEPT_ID, COUNT(*) AS N, AVG(SALARY) AS AVG_SAL FROM EMPLOYEES GROUP BY ALL ORDER BY DEPT_ID;

Result4 rows · 3 columns

DEPT_ID	N	AVG_SAL
10	3	115166.66666667
20	2	95625.00000000
30	2	83950.00000000
NULL	1	92000.00000000

The same four groups again, with an average salary attached, and no grouping key written down anywhere in the query.

Now let's put a second column in the SELECT list

This next query asks for DEPT_ID, MANAGER_ID and the count. The GROUP BY clause is still the same two words.

Query

SELECT DEPT_ID, MANAGER_ID, COUNT(*) AS N FROM EMPLOYEES GROUP BY ALL 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. ALL found both grouping keys without being told how many there were. That is also the same seven rows the previous post got from GROUP BY DEPT_ID, MANAGER_ID, and the same seven episode 9 got from SELECT DISTINCT on those two columns.

The feature is the hazard

Those two grids are the feature and the hazard in one step. The GROUP BY clause is identical in both, and the grouping went from four rows to seven, because the SELECT list changed. That is exactly what ALL is for while you are exploring, and exactly what you do not want in something that runs on a schedule and emails somebody a number. It is the ordinal's hazard again, for the same reason: the grouping is decided by the select list rather than stated.

Can we compare all four spellings?

Two of the grids above are the same query written two ways. GROUP BY 1 and GROUP BY STATUS_UPPER over UPPER(STATUS) returned identical results, because they are the same grouping expressed differently.

Add the written-out expression and ALL and we have four spellings of one idea.

Spelling	What it points at	Portable?	Where it bites
GROUP BY UPPER(STATUS)	The expression itself	Yes	You maintain two identical copies of it
GROUP BY 1	A position in the SELECT list	Mostly	Reorder the select list and the grouping moves, silently
GROUP BY STATUS_UPPER	A column alias	No — Snowflake-specific	Fails the day the query is ported elsewhere
GROUP BY ALL	Every non-aggregate in the SELECT list	No — Snowflake-specific	Add a column anywhere in the select list and the grouping changes

On mixing them

Pick one and be consistent. A file that uses all four spellings is genuinely harder to read than one that repeats an expression, which is the opposite of the problem any of these shortcuts was meant to solve.

Now let's group rows into bands

One more shape, because it is the thing people ask about as soon as they realise you can group by an expression. Putting rows into buckets that the table does not store.

Query

SELECT CASE WHEN SALARY >= 100000 THEN 'six figures' ELSE 'under' END AS BAND, COUNT(*) AS N
FROM EMPLOYEES GROUP BY CASE WHEN SALARY >= 100000 THEN 'six figures' ELSE 'under' END ORDER BY BAND;

Result2 rows · 2 columns

BAND	N
six figures	3
under	5

Two buckets. Three people at six figures and five under, which again adds up to the eight rows of the table. CASE gets covered properly in a later level. It is here because this is the query people want on the day they learn GROUP BY.

Now look at the cost of writing it this way. The entire CASE expression is spelled out twice, which makes it the longest thing in the query by a wide margin.

That is the best argument on the page for the alias. Name the expression BAND once and the GROUP BY clause becomes a single word, with no second copy to keep in step.

One last thing: which one to use

• GROUP BY ALL while you are exploring. In a worksheet where the SELECT list changes every minute, maintaining the GROUP BY clause is pure friction and this removes all of it.
• The alias for anything someone else reads. It names what it groups by, it does not move when the select list moves, and it keeps a long expression in one place.
• The expression written out in full if the query might travel. Neither the alias nor ALL is standard SQL, so a query that may end up on another engine should say exactly what it groups by.
• The ordinal for a throwaway. It is the most compact and the least self-describing, and the thing it points at is the thing most likely to change.

Whichever one we write, the grouping itself has not changed at all. Every grid on this page could have been produced by naming the columns. These are four ways of saying the same thing to the optimiser, chosen for the human who reads the query next.

Key points

• A GROUP BY on an expression normally makes you write that expression twice, once in SELECT and once in GROUP BY, and keep both copies in step.
• GROUP BY 1 groups by a position in the select list. Reorder the list and the grouping moves with no error.
• Snowflake lets you GROUP BY a column alias from the same SELECT list. Most databases reject that, because in standard SQL the select list has not been evaluated yet.
• GROUP BY ALL groups by every item in the select list that is not an aggregate, however many there are.
• Putting MANAGER_ID in the select list of a GROUP BY ALL query took the result from four rows to seven, with the GROUP BY clause untouched. That is the convenience and the hazard in the same step.
• All four spellings produce identical output. They differ only in what happens when somebody edits the query later.
• The alias is the readable choice, ALL is the exploring choice, and the expression written out in full is the portable one.
• A CASE expression is the clearest case for the alias. Written out twice it is the longest line in the query, and aliased it is one word.

Next in this series

In the next post we will cover the HAVING clause. Filtering the groups after we have made them, why a WHERE clause genuinely cannot do it, and the two compilation errors everybody meets on the way to finding that out.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running against the sample data created by the setup script above. The EMPLOYEES and ORDERS data is invented demo data. No performance or benchmark claims are made anywhere in this series: in particular, no claim is made that one of these four spellings executes faster than another, because nothing here measured that. Clause behaviour follows the Snowflake GROUP BY documentation.