In this post we are going to cover LISTAGG in Snowflake.
LISTAGG is an aggregate, the same as COUNT and SUM. The difference is what it hands back. The other aggregates collapse a group of rows into a number. LISTAGG collapses it into a string, with a delimiter of our choosing between the values.
We will start with the simplest call and then work through the things about it that catch people out: the order of the items inside the string, the delimiter we are allowed to leave out, what happens to the NULLs, and what comes back when every value in a group is NULL.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 19 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
- Let's create some sample data
- Let's start with the simplest LISTAGG
- Now let's ask for an order with WITHIN GROUP
- Now let's get one list per group
- Can we use DISTINCT inside it?
- Now let's leave the delimiter out
- Let's see what happens to the NULLs
- One last thing: an all-NULL group gives back an empty string
Let's create some sample data
LISTAGG turns rows into a string, so it matters which rows. Almost everything in this post runs against the EMPLOYEES table, and the script below creates it.
Copy this into a Snowflake worksheet and run it once. Every post in this series uses the same table, so if you have run it already you are ready to go.
-- Sample data for this post.
-- Run this once. Every post in this series uses the same EMPLOYEES table.
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 things in that script are worth noticing before we run the first query, because both of them become traps later. The first is the order the names go in. Aisha, Marcus, Lena, Tomas. That is not alphabetical, and whether LISTAGG follows it is the first thing we deal with.
The second is COMMISSION_PCT, which is NULL for most of these people. Only Lena, Priya and Sofia have a value in it. That column is the whole of the second half of this post.
One query uses the ORDERS table instead. That is the other table from the same shared sample-data script, and the post on SUM, AVG, MIN and MAX lists it in full.
Let's start with the simplest LISTAGG
The whole idea fits in one query. We write LISTAGG, then the column we want, then a delimiter to put between the values.
SELECT LISTAGG(FIRST_NAME, ', ') AS EVERYONE FROM EMPLOYEES;| EVERYONE |
|---|
| Aisha, Marcus, Lena, Tomas, Priya, Daniel, Sofia, Grace |
One row and one column came back, and in that single cell are eight names. That is every employee in the table, in a readable list rather than a count. That is the entire pitch for the function.
Everything else in this post is the things that will catch us out, and the first one is already visible above.
The order is not what it looks like
Read those names again. Aisha, Marcus, Lena, Tomas. That is not alphabetical. It is the order the rows were inserted in, which on this run is also EMP_ID order. That is a coincidence, not a rule. The documentation is blunt about it.
“If you do not specify WITHIN GROUP (ORDER BY), the order of elements within each list is unpredictable.”
Unpredictable means unpredictable. Do not write code that depends on the order you got today. Do not assume it is the order you will get tomorrow on the same data. If the order of the items inside the string matters to anybody, and in a report it always does, you have to ask for it.
Now let's ask for an order with WITHIN GROUP
Here is the same query with WITHIN GROUP (ORDER BY FIRST_NAME) added. Nothing else changed.
SELECT LISTAGG(FIRST_NAME, ', ') WITHIN GROUP (ORDER BY FIRST_NAME) AS ALPHABETICAL FROM EMPLOYEES;| ALPHABETICAL |
|---|
| Aisha, Daniel, Grace, Lena, Marcus, Priya, Sofia, Tomas |
Aisha, Daniel, Grace, Lena. Alphabetical, because that is what we asked for. Same names, same cell, same everything else.
WITHIN GROUP reads oddly at first, so it is worth being precise about what it does. The ORDER BY inside it does not order our result rows. It orders the items inside the string. Ordering the rows of the result is still the query's own ORDER BY clause, as usual. The two are independent and a query can carry both.
Write WITHIN GROUP (ORDER BY ...) essentially always. It costs us ten characters and it is the difference between a list and a list we can trust.
Now let's get one list per group
This is probably the shape we came here for. We add a GROUP BY and we get one string per group instead of one string for the whole table.
SELECT DEPT_ID, LISTAGG(FIRST_NAME, ', ') WITHIN GROUP (ORDER BY FIRST_NAME) AS TEAM
FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID;| DEPT_ID | TEAM |
|---|---|
| 10 | Aisha, Daniel, Marcus |
| 20 | Lena, Tomas |
| 30 | Priya, Sofia |
| NULL | Grace |
One row per department, with the team as a readable list. Department 10 is Aisha, Daniel and Marcus.
Now notice what did not change. Four rows, four departments, and that includes the NULL department with Grace on her own. Those are exactly the same four groups that COUNT and GROUP BY produced earlier in this level. Nothing about the grouping changed. Only the aggregate changed.
That is the useful way to hold LISTAGG in our heads. It is not a different kind of query, it is a different answer to the same question.
Can we use DISTINCT inside it?
We can. DISTINCT goes inside LISTAGG exactly the way it goes inside COUNT.
SELECT LISTAGG(DISTINCT STATUS, ' | ') WITHIN GROUP (ORDER BY STATUS) AS STATUSES FROM ORDERS;| STATUSES |
|---|
| CANCELLED | PENDING | RETURNED | SHIPPED |
Four status values, in one cell, separated by a pipe.
ORDERS holds 41 rows. That count comes from the COUNT episode earlier in this level, not from anything on this page. So without DISTINCT this cell would have been 41 values long with every status repeated. With it, we get the four that exist.
What values actually appear in this column? is a question we ask constantly when somebody hands us an unfamiliar table, and this is the one-line version of it.
Now let's leave the delimiter out
The delimiter is optional. If we leave it out, LISTAGG still runs. It does not complain, and it does not pick a sensible default for us. Here it is over EMP_ID.
SELECT LISTAGG(EMP_ID) WITHIN GROUP (ORDER BY EMP_ID) AS IDS FROM EMPLOYEES;| IDS |
|---|
| 12345678 |
Separate employee IDs, glued into what reads as a single number. If that arrived in a report or in a downstream join key, it would take a while to work out what had happened.
It is not a bug, and it is documented. “If no delimiter is specified, an empty string is used as the delimiter.” An empty string is a perfectly valid separator. It is just not a visible one.
Pass a delimiter unless you genuinely want the values glued together. This is the easiest mistake on the page to make by accident, because the query that causes it is shorter than the query that does not.
Let's see what happens to the NULLs
COMMISSION_PCT is NULL for most of these employees. Let's aggregate it and see what LISTAGG does about that, one list per department.
SELECT DEPT_ID, LISTAGG(COMMISSION_PCT, ',') WITHIN GROUP (ORDER BY EMP_ID) AS COMMISSIONS
FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID;| DEPT_ID | COMMISSIONS |
|---|---|
| 10 | |
| 20 | 0.05 |
| 30 | 0.10,0.08 |
| NULL |
Department 20 is the row to look at. Two people are in it, Lena and Tomas, and only Lena has a commission. The output is 0.05.
The thing worth noticing is what is not there. There is no comma. There is no empty slot where Tomas would have been. LISTAGG did not leave a gap. It left the row out entirely. That missing comma is the whole lesson.
Documented again. “If some but not all input expressions evaluate to NULL, the output contains all non-NULL values and excludes the NULL values.”
Now compare department 30 on the row below. Both Priya and Sofia have a commission there, and we get 0.10,0.08 with the comma present. The delimiter only ever appears between values that are there.
One last thing: an all-NULL group gives back an empty string
Look at department 10 in the grid above. Three people, none of them with a commission, and the cell is blank. The NULL department is blank for the same reason, because Grace has no commission either.
So what is that blank, actually? A NULL, or an empty string?
It is an empty string. “If all input expressions evaluate to NULL, the output is an empty string.”
This is the one place LISTAGG behaves differently from every aggregate we already know, and the difference is small enough to cost us an afternoon.
Aggregate over a group where every value is NULL | What comes back |
|---|---|
SUM(col) | NULL |
AVG(col) | NULL |
MIN(col) / MAX(col) | NULL |
LISTAGG(col, ',') | '' — an empty string |
A test for IS NULL will not find those rows. WHERE COMMISSIONS IS NULL returns nothing, and the blank cells sit there looking exactly like missing data. To spot the empty ones, compare against an empty string with LISTAGG(COMMISSION_PCT, ',') <> '', or check the length.
Key points
LISTAGGis the aggregate that collapses a group of rows into a string instead of a number. Rows in, one sentence out.- Without
WITHIN GROUP (ORDER BY ...)the order of the items inside the string is, in Snowflake's own word, unpredictable. Write it essentially always. - The
ORDER BYinsideWITHIN GROUPorders the items in the string, not the rows of the result. They are separate jobs and a query can do both. - With
GROUP BYwe get one string per group. The same groupsCOUNTgave us, with a different answer in the second column. DISTINCTworks insideLISTAGG, which makes it a one-line way to see what values a column actually holds.- The delimiter is optional and defaults to an empty string, so
LISTAGG(EMP_ID)glues the values together with nothing between them. NULLs are skipped, not blanked. No stray delimiter is left where the missing value was.- A group whose values are all
NULLreturns an empty string, notNULL. That is unlikeSUM,AVG,MINandMAX, andIS NULLwill not find those rows.
In the next post we will look at ARRAY_AGG and OBJECT_AGG. Same idea with one important difference. Instead of a string we get an array or an object back, and both of those are things we can keep working with.
Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the script above. The EMPLOYEES and ORDERS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. The four sentences quoted in italics are from the Snowflake LISTAGG documentation.



No comments:
Post a Comment
Note: Only a member of this blog may post a comment.