Snowflake SQL Tutorial 17 - HAVING in Snowflake

Snowflake SQL · Episode 17

In this post we are going to cover the HAVING clause in Snowflake.

HAVING is a filter, the same as WHERE is. The difference is when it runs and what it looks at. WHERE runs before the grouping and tests one row at a time. HAVING runs after the grouping and tests one group at a time.

Everybody explains it in that one sentence, and the sentence is true and almost useless until you have watched it happen. So we will put both clauses on the same table, one at a time, and read the difference out of the numbers. Then we will try the things that do not work, because the errors explain the rule better than the rule does.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back, including the three statements that failed.

Episode 17 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start with the groups and no filter at all
3. Now let's add a WHERE clause in front of it
4. Now let's try HAVING instead
5. Can we just put the count in the WHERE clause?
6. Now let's look at two more refusals in the same family
7. Can HAVING test something other than COUNT?
8. Now let's answer a real question with it
9. Let's finish with both clauses in one query

Let's create some sample data

This whole page compares two filters doing different jobs on the same table, and that table is EMPLOYEES. The script below creates it and fills it.

Copy this into a Snowflake worksheet and run it once. Then you can run every query on this page yourself.

Sample data — run once

-- 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 details in that script decide most of what happens below. The salaries range widely, which gives a WHERE clause something to bite on. And the last row, Grace, has NULL in DEPT_ID.

That NULL becomes a group of its own, and it is the group that disappears later.

Let's start with the groups and no filter at all

Here is the same grouping we have run for the last two posts. One row per department, with a headcount and an average salary, and no filtering of any kind.

Query

SELECT DEPT_ID, COUNT(*) AS HEADCOUNT, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID;

Result4 rows · 3 columns

DEPT_ID	HEADCOUNT	AVG_SALARY
10	3	115166.66666667
20	2	95625.00000000
30	2	83950.00000000
NULL	1	92000.00000000

Four groups. Department 10 with three people, 20 with two, 30 with two, and the NULL group with one.

Remember those four counts, because the whole episode is about what happens to them.

Now let's add a WHERE clause in front of it

Same grouping, with a WHERE clause before it. Only the people earning more than 90000.

Query

SELECT DEPT_ID, COUNT(*) AS N FROM EMPLOYEES WHERE SALARY > 90000 GROUP BY DEPT_ID ORDER BY DEPT_ID;

Result4 rows · 2 columns

DEPT_ID	N
10	3
20	1
30	1
NULL	1

Still four groups. Nothing has disappeared. But the counts have moved. Department 20 has gone from two people to one, and 30 has done the same.

What changed is what is inside each group, because WHERE removed rows before the grouping ever happened. By the time GROUP BY ran, those two employees were simply not in the data any more.

So WHERE decides what goes into each group.

Now let's try HAVING instead

No WHERE clause at all this time. Just HAVING COUNT(*) > 1 on the end of the same grouping.

Query

SELECT DEPT_ID, COUNT(*) AS HEADCOUNT FROM EMPLOYEES GROUP BY DEPT_ID HAVING COUNT(*) > 1 ORDER BY DEPT_ID;

Result3 rows · 2 columns

DEPT_ID	HEADCOUNT
10	3
20	2
30	2

Three rows. Every count is exactly what it was in the first grid, three and two and two, untouched. What is missing is a whole group. The NULL group, whose count of one failed the test, is gone.

And there is the whole distinction, in numbers rather than in a sentence somebody asked you to memorise. WHERE changed what was in each group and kept all four of them. HAVING kept every count exactly as it was and removed one group completely.

	WHERE	HAVING
Runs	Before GROUP BY	After GROUP BY
Tests	One row at a time	One group at a time
Can refer to an aggregate	No — there are no groups yet	Yes — that is what it is for
Effect on the counts	Changes them	Leaves them exactly as they were
Effect on the group list	Every group survives	Groups can disappear

WHERE decides what goes into each group. HAVING decides which groups survive.

Can we just put the count in the WHERE clause?

That is the obvious question. Why do we need a second keyword at all? Let's try it and see what Snowflake says.

Query — this one fails

SELECT DEPT_ID, COUNT(*) AS N FROM EMPLOYEES WHERE COUNT(*) > 1 GROUP BY DEPT_ID;

Query failed — Snowflake returned an error

002035 (42601): SQL compilation error:
Invalid aggregate function in where clause [COUNT(*)]

That message is worth understanding rather than memorising, because once we see why it happens we never have to remember the rule again. It falls straight out of the order the clauses run in.

1. FROM — get the rows
2. WHERE — throw some of them away
3. GROUP BY — collapse what is left into groups
4. HAVING — throw some of the groups away
5. SELECT — work out the columns and their aliases
6. ORDER BY — sort what is left

Look at where WHERE sits. It runs before GROUP BY. At the moment the WHERE clause is evaluated the rows have not been grouped yet. So there are no groups, so there is no count, so there is nothing there to compare against anything.

HAVING sits after GROUP BY. By the time it runs, the groups exist and each one has a count. So HAVING can ask the question and WHERE genuinely cannot.

Two other episodes, for free

That one list quietly explains two earlier episodes for free. ORDER BY can use a column alias because it runs after SELECT, which is where aliases come into existence. That was episode 10. And in episode 13, a WHERE clause filtered the rows before COUNT counted them, because WHERE is upstream of the aggregate. Same list, same reason.

Now let's look at two more refusals in the same family

You will meet this next one constantly. Here FIRST_NAME is in the SELECT list, but it is neither aggregated nor in the GROUP BY clause.

Query — this one fails

SELECT DEPT_ID, FIRST_NAME, COUNT(*) AS N FROM EMPLOYEES GROUP BY DEPT_ID;

Query failed — Snowflake returned an error

001104 (42601): SQL compilation error: error line 1 at position 16
'EMPLOYEES.FIRST_NAME' in select clause is neither an aggregate nor in the group by clause.

Snowflake has to produce one row per department, and department 10 has three different first names in it. There is no way to pick one of them, so it refuses. And it is right to. A database that guessed here would be a worse database.

The fix is not a trick for getting the statement past the compiler. It is deciding what we actually meant.

• One row per person — drop the GROUP BY clause.
• One row per department — aggregate the name, with MIN(FIRST_NAME) or by collecting them all into one value with LISTAGG, which gets its own post shortly.
• One row per department and name — GROUP BY DEPT_ID, FIRST_NAME.

Now let's try the same mistake with no GROUP BY at all

Here is the same problem in a different costume. A bare column sitting next to an aggregate, with no GROUP BY anywhere in the statement.

Query — this one fails

SELECT FIRST_NAME, COUNT(*) AS N FROM EMPLOYEES;

Query failed — Snowflake returned an error

000979 (42601): SQL compilation error:
[EMPLOYEES.FIRST_NAME] is not a valid group by expression

COUNT(*) collapses the whole table to one row. FIRST_NAME has a different value on every row of the table and no instruction about which one to keep. Different message, identical underlying problem.

The question behind both errors

If you find yourself arguing with either of these errors, the question to ask is not how do I make Snowflake accept this. It is how many rows did I actually want back, because that is the question the statement has failed to answer.

Can HAVING test something other than COUNT?

It can. HAVING is not a COUNT feature, and it will test any aggregate we could put in the SELECT list. Let's keep only the departments whose average salary is over 95000.

Query

SELECT DEPT_ID, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEES GROUP BY DEPT_ID HAVING AVG(SALARY) > 95000 ORDER BY DEPT_ID;

Result2 rows · 2 columns

DEPT_ID	AVG_SALARY
10	115166.66666667
20	95625.00000000

Two groups survive. Check them against the first grid on this page. 115166.66666667 and 95625.00000000 are over the line, while 83950.00000000 and 92000.00000000 are not.

Now let's filter on an aggregate we are also displaying

The aggregate HAVING tests does not have to be one we are displaying. In this query the groups are kept or dropped on SUM(AMOUNT) being over 5000, and the total is in the SELECT list as well.

Query

SELECT STATUS, COUNT(*) AS N, SUM(AMOUNT) AS TOTAL FROM ORDERS GROUP BY STATUS HAVING SUM(AMOUNT) > 5000 ORDER BY STATUS;

Result2 rows · 3 columns

STATUS	N	TOTAL
PENDING	3	7540.00
SHIPPED	34	192307.00

Two statuses come back. Because the total is on display, we can check the arithmetic for ourselves. But it did not have to be there. Delete SUM(AMOUNT) AS TOTAL from the select list and the query still works, and then the number that decided which rows we are looking at appears nowhere in the output.

The invisible filter

That is occasionally exactly what you want and occasionally very confusing for whoever reads the report, because the groups that failed the test are missing from it with nothing on screen to explain why. If you filter on an aggregate you are not displaying, say so in a comment next to the HAVING clause.

Now let's answer a real question with it

Here is why HAVING is worth knowing for practical reasons rather than academic ones. Which customers have placed at least five orders? Group by the customer, count the orders, and filter on the count.

Query

SELECT CUSTOMER_ID, COUNT(*) AS ORDERS FROM ORDERS GROUP BY CUSTOMER_ID HAVING COUNT(*) >= 5 ORDER BY ORDERS DESC;

Result5 rows · 2 columns

CUSTOMER_ID	ORDERS
101	9
102	6
107	5
103	5
105	5

Five customers clear the bar, with 101 at the top on nine orders. That question cannot be answered with a WHERE clause at all, in any database, because the thing being filtered on does not exist until after the grouping has happened.

Anything of the form which keys have more than N of something is this query. Duplicate records, customers above a threshold, products ordered more than once, values appearing in two places at the same time. It is the same four lines every time.

Let's finish with both clauses in one query

WHERE and HAVING are not alternatives. Most real queries use both, doing different jobs in the same statement.

In this one the WHERE clause keeps the people earning over 80000, and the HAVING clause then keeps the departments that still have more than one person left.

Query

SELECT DEPT_ID, COUNT(*) AS N, AVG(SALARY) AS AVG_SAL FROM EMPLOYEES
WHERE SALARY > 80000 GROUP BY DEPT_ID HAVING COUNT(*) > 1 ORDER BY DEPT_ID;

Result2 rows · 3 columns

DEPT_ID	N	AVG_SAL
10	3	115166.66666667
20	2	95625.00000000

Two rows, and the query reads as a sentence. Out of the people earning over 80000, which departments have more than one of them? The WHERE clause did the first half, the HAVING clause did the second, and neither could have done the other's job.

One last thing, in case episode 10 is bothering you

Episode 10 showed that Snowflake does let us use a column alias in a WHERE clause, which most databases reject on the grounds that the select list has not been evaluated yet. Sitting next to everything on this page, that can look like a contradiction.

It is not, and the difference is worth holding on to.

	What it is	Why the clause order matters
An alias	A name for an expression	A name can be resolved early — Snowflake looks it up and substitutes the expression
An aggregate	A value that does not exist until the rows are grouped	There is nothing to count yet, so there is no value to resolve at any price

Snowflake being generous about names does not mean it can invent a number out of order.

Key points

• WHERE decides what goes into each group. HAVING decides which groups survive. Both filter, at different moments, on different things.
• On this table WHERE SALARY > 90000 changed two of the counts and kept all four groups, while HAVING COUNT(*) > 1 left every count untouched and removed a group entirely.
• An aggregate in a WHERE clause is a compilation error, and not an arbitrary one. WHERE runs before GROUP BY, so there is no count in existence to compare.
• The clause order — FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY — turns most of this from a rule into a consequence.
• A column in the SELECT list that is neither aggregated nor grouped is an error, because the statement has not said which of several values to keep.
• HAVING tests any aggregate, including one that is not in the SELECT list, which hides the reason a group is missing from the person reading the report.
• Which keys have at least N of something is the query shape HAVING exists for, and a WHERE clause cannot express it in any database.
• Snowflake allowing a column alias in WHERE does not contradict any of this. An alias is a name that can be resolved early, an aggregate is a value that does not exist yet.

Next in this series

In the next post we will cover ROLLUP, CUBE and GROUPING SETS. Subtotals and grand totals in one pass, without writing the same query three times and stitching the results together with UNION ALL.

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, including the three statements that failed — each error pane carries the text Snowflake returned, not a paraphrase. The EMPLOYEES and ORDERS data is invented demo data. No performance or benchmark claims are made anywhere in this series, and nothing here measures whether filtering in WHERE or in HAVING is cheaper. Clause behaviour follows the Snowflake HAVING documentation.