GROUP BY ALL in Snowflake

1. Introduction

The GROUP BY clause is a familiar clause for folks who have been familiar with SQL already. It is used with a SELECT statement to collect data from multiple records and aggregate the results by one or more columns.

In SQL statements, when you use a GROUP BY clause, you typically need to specify the fields in the SELECT clause that you want to aggregate the data on, and those same fields must be included in the GROUP BY clause.

2. GROUP BY ALL in Snowflake

The GROUP BY ALL clause is similar to the GROUP BY clause except that it automatically includes all non-aggregate items in the SELECT clause for grouping, eliminating the need to specifying them again for data grouping.

Let us understand with an example.

The following SQL query calculates the total revenue and quantity of products sold on each day by shipping mode.

SELECT
    L_SHIPDATE, L_SHIPMODE,
    SUM(L_EXTENDEDPRICE) AS REVENUE,
    SUM(L_QUANTITY) AS QUANTITY
FROM LINEITEM
GROUP BY L_SHIPDATE, L_SHIPMODE
;

The above query can be re-written using the GROUP BY ALL clause as shown below.

SELECT
    L_SHIPDATE, L_SHIPMODE,
    SUM(L_EXTENDEDPRICE) AS REVENUE,
    SUM(L_QUANTITY) AS QUANTITY
FROM LINEITEM
GROUP BY ALL
;

This eliminates the need for explicitly specifying the list of columns in the GROUP BY clause for grouping. Instead, we can use GROUP BY ALL.

3. Referencing Non-Aggregate Expressions

The GROUP BY ALL clause can also reference all the non-aggregate expressions specified in the SELECT clause.

For example, the following query calculates the total revenue and quantity of products sold on each year by shipping mode.

SELECT
    YEAR(L_SHIPDATE) AS YEAR, L_SHIPMODE,
    SUM(L_EXTENDEDPRICE) AS REVENUE,
    SUM(L_QUANTITY) AS QUANTITY
FROM LINEITEM
GROUP BY ALL
;

The above query is equivalent to the following query.

SELECT
    YEAR(L_SHIPDATE) AS YEAR, L_SHIPMODE,
    SUM(L_EXTENDEDPRICE) AS REVENUE,
    SUM(L_QUANTITY) AS QUANTITY
FROM LINEITEM
GROUP BY YEAR(L_SHIPDATE), L_SHIPMODE
;

As the requirement is to calculate sales and quantity by year, we have used the YEAR function to extract the year information from shipping date fields. The GROUP BY ALL automatically identifies it as a non-aggregate expression in the SELECT clause and includes it for grouping the data.

4. Removing Duplicates using GROUP BY ALL

The GROUP BY clause can be used to retrieve unique records from a table by applying it on all table fields. Now to remove duplicates, instead of specifying all the table fields in the GROUP BY clause, we can use GROUP BY ALL.

For example, we have an employee table with duplicate records as shown below.

The unique records can be extracted using GROUP BY ALL as shown below.

SELECT * FROM EMPLOYEE GROUP BY ALL;

The above query is equivalent to the following queries.

SELECT * FROM EMPLOYEE GROUP BY EMPLOYEE_ID, NAME, SALARY;

SELECT
  EMPLOYEE_ID, NAME, SALARY
FROM EMPLOYEE
GROUP BY EMPLOYEE_ID, NAME, SALARY;

5. Conclusion

GROUP BY ALL clause is such a simple yet powerful quality-of-life improvement from Snowflake which makes developers life easy. Next time you find yourself working with the GROUP BY clause, consider incorporating GROUP BY ALL into your code. This valuable addition can simplify your queries and increases readability of your code.

Subscribe to our Newsletter !!

Related Articles: