Snowflake GROUP BY ALL — Unlocked

Published in

Snowflake Builders Blog: Data Engineers, App Developers, AI/ML, & Data Science

3 min readJul 16, 2023

In this blog we are going to explore how we can utilize the Snowflake grouping clause. Hope everyone should know or hear what is GROUP BY clause in SQL if you are SQL background. But don't worry if you have not heard about this in the past. Today we will unlock this for you and make sure it's clear for each of my audience.

What is Group By Clause?

Group By clause is not a new thing in SQL world, it's been decades old SQL capability which was used to group the item in certain category to visualize the aggregation result. This is a very basic SQL feature used by majority of reporting, visualization or analytical team to present their data in more meaning full manner.

Below is the basic use-case of group by clause

SELECT <COLUM_NAME1>,
       <COLUM_NAME2>
       SUM(<COLUM_NAME3>)
FROM <TABLE_NAME>
GROUP BY <COLUM_NAME1>,
         <COLUM_NAME2>;

Now, instead of putting column name in GROUP BY clause we can also use the numeric like 1,2,3 etc. which will reference the position of column in your select clause.

SELECT <COLUM_NAME1>,
       <COLUM_NAME2>
       SUM(<COLUM_NAME3>)
FROM <TABLE_NAME>
GROUP BY 1, 2;

What’s new enhancement to GROUP BY clause?

There has been new enhancement to GROUP BY clause for helping reduce the effort to either put column name or position in group by clause. This is going to show improvement where you have complex SQL with lots of columns to type in group by clause.

Snowflake has introduced an “ALL” keyword to bypass the column name in group by clause. So now instead of providing a long list of non-aggregate column list in group by just put ‘GROUP BY ALL’ and you are done.

Snowflake will automatically detect the list of column and column group by positioning based on your projection i.e. select column list automatically.

SELECT <COLUM_NAME1>,
       <COLUM_NAME2>,
       <COLUM_NAME3>,
       <COLUM_NAME4>,
       <COLUM_NAME5>,
       SUM(<COLUM_NAME6>)
FROM <TABLE_NAME>
GROUP BY ALL;

Now you see life is so simple with this small improvement….! Isn't’d ?

Hope this blog helps you to get insight into the Snowflake GROUP BY ALL feature. If you are interested in learning more details about Snowflake Group By , you can refer to Snowflake documentation. Feel free to ask a question in the comment section if you have any doubts regarding this. Give a clap if you like the blog. Stay connected to see many more such cool stuff. Thanks for your support.

You Can Find Me:

Subscribe to my YouTube Channel: https://www.youtube.com/c/RajivGuptaEverydayLearning

Follow me on Medium: https://rajivgupta780184.medium.com/

Follow me on Twitter: https://twitter.com/RAJIVGUPTA780

Connect with me in LinkedIn: https://www.linkedin.com/in/rajiv-gupta-618b0228/