Snowflake Supports GROUP BY ALL

Snowflake just launched a new syntax: GROUP BY ALL. This blog goes through the use cases and when you should not use it.

Photo by Luca Bravo on Unsplash

Use Cases

The GROUP BY clause groups rows with the same group-by-item expressions and computes aggregate functions for the resulting groups. Even though it’s not mandatory, it is a common practice to include all non-aggregated expressions in the SELECT clause within the GROUP BY clause. When there is a change to the SELECT clause of a query, typically, the GROUP BY clause needs modification as well.

That’s why Snowflake is introducing the GROUP BY ALL syntax. Simply put, Snowflake GROUP BY ALL provides a shortcut which detects all non-aggregated expressions in the SELECT clause, and replaces ALL with these expressions. For example, the following query:

SELECT A+B, B, COUNT(*), SUM(C)
FROM T
GROUP BY ALL;

Is equivalent to the following query:

SELECT A+B, B, COUNT(*), SUM(C)
FROM T
GROUP BY A+B, B;

This largely reduces the need for manually copying and pasting the expressions or numbering the positions from the SELECT clause. This syntax also maintains the syntactic correctness of a query when it’s updated.

Caveats about the new syntax

While the GROUP BY ALL clause can be applied in the majority of the queries, there are certain cases you should pay attention to.

1. GROUP BY ALL doesn’t support GROUPING SETS, ROLLUP and CUBE.
2. GROUP BY ALL doesn’t work in conjunction with other GROUP BY expressions. For example, “GROUP BY ALL, A” is not a valid syntax.
3. GROUP BY ALL only handles whole expressions from the SELECT clause. In the example above, SELECT A+B will result in GROUP BY A+B, instead of GROUP BY A, B.
4. When all expressions in the SELECT clause are aggregated expressions, you shouldn’t have a GROUP BY clause in your query. But if you do, it will be treated as if there is no GROUP BY clause.
5. Exercise caution when using GROUP BY ALL in conjunction with window functions. If a window function call does not contain any aggregation arguments, it is treated as a non-aggregated expression and thus appears in the expansion of GROUP BY ALL. While this doesn’t invalidate the GROUP BY ALL syntax, execution of the query would fail because a window function is not a valid GROUP BY expression.

Please refer to the Snowflake Documentation for more syntax details.

Happy grouping!

Felipe Hoffa on LinkedIn: #snowflakesummit | 91 comments