In this post we are going to cover the GROUP BY clause in Snowflake.
Every aggregate we have written so far has answered one question about a whole table. One COUNT, one SUM, one number. GROUP BY splits the rows into groups first, and then the same aggregate answers the same question once per group.
We will start by looking at the rows with no grouping at all, so we can see where the groups come from. Then we will group them one step at a time: one column, several aggregates, two columns, the NULL, and finally a group that the table does not store anywhere.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 15 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
Let's create some sample data
Most of this page runs against EMPLOYEES, and the column we are going to group on is DEPT_ID. The script below creates that table and fills it.
Copy this into a Snowflake worksheet and run it once. It is the same table the whole series uses, so you only set it up one time.
-- 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');Two queries later on this page use ORDERS and CUSTOMERS instead. Those come from the same shared sample-data script, and the previous post on SUM and AVG prints the ORDERS half of it in full.
Now let's look at the two columns we care about
Here are just the department and the salary of everyone in the table, with no grouping of any kind.
SELECT DEPT_ID, SALARY FROM EMPLOYEES ORDER BY DEPT_ID;| DEPT_ID | SALARY |
|---|---|
| 10 | 118000.00 |
| 10 | 96500.00 |
| 10 | 131000.00 |
| 20 | 104000.00 |
| 20 | 87250.00 |
| 30 | 96500.00 |
| 30 | 71400.00 |
| NULL | 92000.00 |
Eight rows, one per employee. Now read the DEPT_ID column downwards and count the repeats. 10 appears three times, 20 twice, 30 twice, and there is a single NULL.
So there are four different values in that column, counting the NULL. Hold on to that number. It is the answer to the next query before we run it.
Let's start with GROUP BY on one column
Same table, same column. This time with GROUP BY DEPT_ID and a COUNT on the end.
SELECT DEPT_ID, COUNT(*) AS HEADCOUNT FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID;| DEPT_ID | HEADCOUNT |
|---|---|
| 10 | 3 |
| 20 | 2 |
| 30 | 2 |
| NULL | 1 |
Eight rows went in. Four rows came out. One row for each different value in the column we grouped by, which is exactly the four values we counted by hand a moment ago.
Now have a look at what COUNT(*) is doing. In episode 13 it counted the whole table and gave us one number. Here it is counting inside each group: three in department 10, two in 20, two in 30, and one in the NULL group.
Add those four counts together and we are back at the eight rows we started with. Nothing was dropped and nothing was counted twice.
That is the whole of GROUP BY, and everything else on this page is detail on top of it. Many rows go in. One row per distinct value of the grouping column comes out. Every aggregate in the SELECT list is computed over one group at a time rather than over the table.
Now let's ask more than one question per group
We are not limited to one aggregate. Every aggregate in the SELECT list is computed per group, so we can ask several things about each department in one pass.
SELECT DEPT_ID, COUNT(*) AS HEADCOUNT, SUM(SALARY) AS PAYROLL, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID;| DEPT_ID | HEADCOUNT | PAYROLL | AVG_SALARY |
|---|---|---|---|
| 10 | 3 | 345500.00 | 115166.66666667 |
| 20 | 2 | 191250.00 | 95625.00000000 |
| 30 | 2 | 167900.00 | 83950.00000000 |
| NULL | 1 | 92000.00 | 92000.00000000 |
Department 10 has three people, a payroll of 345500.00 and an average salary of 115166.66666667. Department 30 has two people and an average of 83950.00000000.
Every one of those numbers is computed inside its own group. The average for department 10 knows nothing at all about department 30. The grouping has partitioned the table, and each aggregate sees only its own part of it.
AVG came back with eight decimal places from a column that stores two. That is division widening the numeric scale, which the previous post covered. It is not something GROUP BY did.
Now let's try the same shape on a bigger table
Here it is on ORDERS, grouped by STATUS. This one is worth reading as a result rather than as a syntax example, because it is the reason anybody groups anything.
SELECT STATUS, COUNT(*) AS ORDERS, SUM(AMOUNT) AS TOTAL FROM ORDERS GROUP BY STATUS ORDER BY STATUS;| STATUS | ORDERS | TOTAL |
|---|---|---|
| CANCELLED | 2 | 3750.00 |
| PENDING | 3 | 7540.00 |
| RETURNED | 2 | 1830.00 |
| SHIPPED | 34 | 192307.00 |
SHIPPED accounts for 34 orders worth 192307.00. Everything else in that grid is tiny next to it.
An ungrouped COUNT and SUM over the same table would have given us one row and two numbers. Both perfectly correct, with no hint that a single status accounted for almost all of the total. Grouping is how we find out that our one number was mostly about one thing.
Now let's group by two columns
Put two columns in the GROUP BY clause and we no longer get one row per department. We get one row per combination of the two.
SELECT DEPT_ID, MANAGER_ID, COUNT(*) AS N FROM EMPLOYEES GROUP BY DEPT_ID, MANAGER_ID ORDER BY DEPT_ID, MANAGER_ID;| 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, not four. Department 10 has split into two rows, because its three people do not all report to the same manager. The same thing has happened to 20 and to 30.
We have seen that number before
Seven should feel familiar. In episode 9, SELECT DISTINCT DEPT_ID, MANAGER_ID on this same table also returned seven rows. That was the query used there to show that DISTINCT applies to the whole select list rather than to one column.
That is not a coincidence. The groups of a GROUP BY are exactly the rows of a SELECT DISTINCT on the same columns. Same set of keys, different purpose. DISTINCT hands us the keys. GROUP BY hands us the keys with an answer attached to each one.
There is a practical trick in that. If you want to know what your groups are going to be before you have written a single aggregate, run SELECT DISTINCT on the grouping columns and look at the result. That is your group list, and its row count is the row count your report will have.
Can NULL be a group?
Grace has no department. GROUP BY does not drop her, and it does not fold her in with anybody else. Let's run the grouping again and look at the bottom of the grid.
SELECT DEPT_ID, COUNT(*) AS N FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID NULLS LAST;| DEPT_ID | N |
|---|---|
| 10 | 3 |
| 20 | 2 |
| 30 | 2 |
| NULL | 1 |
The NULL gets a group of its own. This matters more than it looks, because it completes a picture this course has been building for three episodes.
The same NULL, in the same column, treated three different ways by three different features. All three are consistent once you see them together.
| Episode | Query | Result |
|---|---|---|
| 9 | SELECT DISTINCT DEPT_ID | 4 rows — the NULL is one of the values |
| 13 | COUNT(DISTINCT DEPT_ID) | 3 — COUNT throws the NULL away |
| 15 | GROUP BY DEPT_ID | 4 groups — the NULL is a group |
So NULL counts as a value when we are de-duplicating and when we are grouping. The only place it gets discarded is inside COUNT of a column.
Now let's see why that is the behaviour we want
Here is the same thing on CUSTOMERS, another table in the same demo database, grouped by country.
SELECT COUNTRY, COUNT(*) AS CUSTOMERS FROM CUSTOMERS GROUP BY COUNTRY ORDER BY COUNTRY NULLS LAST;| COUNTRY | CUSTOMERS |
|---|---|
| Canada | 2 |
| UK | 2 |
| USA | 5 |
| NULL | 1 |
Three countries, and one customer whose country was never recorded, in a group of its own. Add the CUSTOMERS column up and we get ten, which is the whole table.
If GROUP BY had silently dropped the NULL group, that same column would have added up to nine and the grid would have looked completely reasonable. A customer would have vanished from a report with nothing on screen to tell you. Keeping NULL as a group is what makes the total still reconcile.
Now let's group by something the table does not store
This is the one you will use most, and it is the point where GROUP BY stops being about columns at all. We can group by any expression, including one that invents a value the table does not hold.
ORDERS has an ORDER_DATE. It has no month column. DATE_TRUNC makes one by flattening every date in a month down to the first of that month, and then that is what we group by.
SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH, COUNT(*) AS ORDERS, SUM(AMOUNT) AS TOTAL FROM ORDERS GROUP BY DATE_TRUNC('MONTH', ORDER_DATE) ORDER BY MONTH LIMIT 8;| MONTH | ORDERS | TOTAL |
|---|---|---|
| 2023-01-01 | 2 | 5090.50 |
| 2023-02-01 | 2 | 7650.00 |
| 2023-03-01 | 2 | 7330.75 |
| 2023-04-01 | 2 | 9010.00 |
| 2023-05-01 | 2 | 3980.25 |
| 2023-06-01 | 2 | 10075.50 |
| 2023-07-01 | 2 | 7170.00 |
| 2023-08-01 | 2 | 12110.00 |
One row per month, with the order count and the total for each. That is every monthly report you have ever seen, in one query. The grouping key did not exist until the query invented it.
The query ends in LIMIT 8, so the grid above is the first eight months rather than the whole history. That keeps the page short and changes nothing about how the grouping works.
Read that query as a piece of writing rather than as SQL and something stands out. DATE_TRUNC('MONTH', ORDER_DATE) is written twice. Once in the SELECT list, where it makes the column, and once in the GROUP BY clause, where it defines the groups. Both copies have to stay identical for ever. Change one and forget the other and, at best, you get an error. At worst you get a different report with no complaint from anybody.
That duplication is not a mistake. It is how GROUP BY is defined. Snowflake does give us three ways out of it though, and they are the whole of the next post.
One last thing: what GROUP BY actually changed
GROUP BY changes the shape of our answer. Before it, an aggregate gave us one row about a whole table. After it, the same aggregate gives us one row per group, and the number of rows we get back is the number of distinct values in the columns we grouped by.
Everything else on this page follows from that one sentence. It is why NULL has to be a group, why two grouping columns give us a combination count, and why the same set of keys turns up when we run SELECT DISTINCT instead.
Key points
GROUP BYcollapses many rows into one row per distinct value of the grouping column. Eight rows in, four rows out on this table.- Every aggregate in the
SELECTlist is computed inside its own group. The average for one department knows nothing about any other department. - Group by two columns and you get one row per combination, which is why this table gives seven rows rather than four.
- The groups of a
GROUP BYare exactly the rows of aSELECT DISTINCTon the same columns, soDISTINCTis how you preview your group list. NULLis a group. It is a value forDISTINCTand a group here. The only place it gets thrown away is insideCOUNTof a column.- Keeping the
NULLgroup is what makes a grouped report still add up to the table it came from. - You can group by an expression, not just a column.
DATE_TRUNCon a date is how a monthly report gets built out of a table with no month column. - An expression you group by is written twice, once in
SELECTand once inGROUP BY, and the two copies have to stay in step.
In the next post we will cover GROUP BY ALL, ordinals and aliases. Three shortcuts that stop you writing the same expression twice, one of which most other databases do not have, and the failure mode each one brings with it.
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, ORDERS and CUSTOMERS data is invented demo data. No performance or benchmark claims are made anywhere in this series, and no claim is made about the total row count of ORDERS, because this episode captured no result that shows it. Clause behaviour follows the Snowflake GROUP BY documentation.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.