In this post we are going to cover COUNT in Snowflake. It is the first post of Level 2, which is all about aggregation.
Level 1 was about choosing rows and columns. Every query we wrote returned some of the rows in a table. An aggregate does something different. It takes a whole set of rows and collapses them into a single value. Many rows go in, one row comes out. That is the one idea the whole level shares.
COUNT is the simplest aggregate there is, which is why it goes first. It is also the one that most often hands people a number they did not expect. We will count one table three different ways and get three different numbers, and all three of them will be correct.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 13 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
Let's create some sample data
Most of this page works on a new table called ORDERS. It is part of the same shared sample-data script that created EMPLOYEES back in episode 1.
Copy this into a Snowflake worksheet and run it once. Then you can run every query on this page yourself.
-- Creates the ORDERS table that Level 2 works on.
-- Part of the shared sample-data script; run it once.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;
CREATE OR REPLACE TABLE ORDERS (
ORDER_ID INT,
CUSTOMER_ID INT,
ORDER_DATE DATE,
SHIP_DATE DATE,
STATUS VARCHAR,
AMOUNT NUMBER(10,2),
QUANTITY INT
);
INSERT INTO ORDERS VALUES
(1001, 101, '2023-01-12', '2023-01-15', 'SHIPPED', 4200.00, 12),
(1002, 102, '2023-01-28', '2023-02-02', 'SHIPPED', 890.50, 3),
(1003, 101, '2023-02-14', '2023-02-16', 'SHIPPED', 6150.00, 20),
(1004, 103, '2023-02-27', NULL, 'CANCELLED', 1500.00, 5),
(1005, 104, '2023-03-09', '2023-03-14', 'SHIPPED', 2310.75, 8),
(1006, 101, '2023-03-22', '2023-03-24', 'SHIPPED', 5020.00, 16),
(1007, 105, '2023-04-05', '2023-04-11', 'SHIPPED', 7890.00, 25),
(1008, 102, '2023-04-19', '2023-04-23', 'SHIPPED', 1120.00, 4),
(1009, 103, '2023-05-02', '2023-05-06', 'SHIPPED', 3340.25, 11),
(1010, 106, '2023-05-21', NULL, 'PENDING', 640.00, 2),
(1011, 101, '2023-06-08', '2023-06-10', 'SHIPPED', 8100.00, 27),
(1012, 104, '2023-06-25', '2023-06-30', 'SHIPPED', 1975.50, 7),
(1013, 105, '2023-07-11', '2023-07-15', 'SHIPPED', 6420.00, 21),
(1014, 102, '2023-07-29', '2023-08-03', 'RETURNED', 750.00, 3),
(1015, 107, '2023-08-14', '2023-08-17', 'SHIPPED', 9250.00, 30),
(1016, 103, '2023-08-30', '2023-09-04', 'SHIPPED', 2860.00, 9),
(1017, 101, '2023-09-16', '2023-09-19', 'SHIPPED', 5540.00, 18),
(1018, 106, '2023-10-03', '2023-10-08', 'SHIPPED', 1430.00, 5),
(1019, 105, '2023-10-22', '2023-10-26', 'SHIPPED', 7015.00, 23),
(1020, 107, '2023-11-07', '2023-11-10', 'SHIPPED', 10400.00, 34),
(1021, 102, '2023-11-24', '2023-11-29', 'SHIPPED', 980.00, 4),
(1022, 104, '2023-12-09', NULL, 'CANCELLED', 2250.00, 8),
(1023, 101, '2023-12-27', '2023-12-30', 'SHIPPED', 6730.00, 22),
(1024, 103, '2024-01-15', '2024-01-18', 'SHIPPED', 3910.00, 13),
(1025, 107, '2024-01-31', '2024-02-05', 'SHIPPED', 11200.00, 36),
(1026, 105, '2024-02-17', '2024-02-21', 'SHIPPED', 8340.00, 27),
(1027, 106, '2024-03-04', '2024-03-09', 'SHIPPED', 1690.00, 6),
(1028, 102, '2024-03-21', '2024-03-25', 'SHIPPED', 1240.00, 5),
(1029, 101, '2024-04-06', '2024-04-09', 'SHIPPED', 7460.00, 24),
(1030, 109, '2024-04-23', '2024-04-28', 'SHIPPED', 5180.00, 17),
(1031, 103, '2024-05-10', '2024-05-14', 'SHIPPED', 4025.00, 14),
(1032, 107, '2024-05-28', '2024-06-01', 'SHIPPED', 12850.00, 41),
(1033, 105, '2024-06-13', NULL, 'PENDING', 6900.00, 22),
(1034, 109, '2024-06-30', '2024-07-04', 'SHIPPED', 4760.00, 16),
(1035, 101, '2024-07-18', '2024-07-21', 'SHIPPED', 8820.00, 29),
(1036, 104, '2024-08-02', '2024-08-07', 'SHIPPED', 2540.00, 9),
(1037, 107, '2024-08-25', '2024-08-29', 'SHIPPED', 13100.00, 43),
(1038, 109, '2024-09-11', '2024-09-15', 'SHIPPED', 5930.00, 19),
(1039, 102, '2024-09-29', '2024-10-03', 'RETURNED', 1080.00, 4),
(1040, 101, '2024-10-16', '2024-10-19', 'SHIPPED', 9640.00, 31);
-- One deliberate NULL amount, inserted separately so it is easy to see why it
-- is there: it exists so that COUNT(*), COUNT(AMOUNT) and SUM() give
-- different answers.
INSERT INTO ORDERS VALUES
(1041, 106, '2024-11-05', NULL, 'PENDING', NULL, 3);Seven columns. An order ID, the customer who placed it, an order date, a ship date, a status, an amount and a quantity. Two details in that script matter for this page, and both are deliberate.
- The
AMOUNTof the last order isNULL— the order exists, but no amount was ever recorded against it. That single missing value is what makes two of the counts below disagree. - The same
CUSTOMER_IDappears on many rows, because customers place more than one order. That is what makesCOUNT(DISTINCT ...)worth having.
This page shows no SELECT * of ORDERS, because the table is too long to be worth a screenful of scrolling and because the whole point of the episode is getting its size out of a query instead of out of your eyes. Every number quoted below comes from a result grid on this page.
Let's start with COUNT(*)
The simplest aggregate there is. COUNT(*) over the whole table.
SELECT COUNT(*) AS TOTAL_ROWS FROM ORDERS;| TOTAL_ROWS |
|---|
| 41 |
Now look at what happened to the shape of the answer, not just its value. The table has that many rows in it, and what came back is one row with one number. No aggregate we meet in this level behaves differently.
COUNT(*) means exactly what it says. Count the rows. It does not look inside them. It does not care whether any particular column has a value. It counts how many rows there are and stops.
Now let's put a column name in the brackets
Let's take the asterisk out and put the AMOUNT column in its place.
SELECT COUNT(AMOUNT) AS NON_NULL_AMOUNTS FROM ORDERS;| NON_NULL_AMOUNTS |
|---|
| 40 |
40. One less than COUNT(*) returned, from exactly the same rows. Nothing was filtered and nothing errored.
The reason is the one we met in episode 6. COUNT(column) counts the rows where that column has a value, and one order has no amount recorded against it. COUNT ignores that row, because there is nothing there to count.
Both numbers are correct. They are answers to two different questions:
| Query | The question it answers |
|---|---|
COUNT(*) | How many orders do we have? |
COUNT(AMOUNT) | How many orders have an amount we could add up? |
If somebody asks you for the average order value, that one-row difference decides whether you divide by 40 or by 41 — and one of those answers is wrong. Episode 14 puts both versions side by side and shows exactly how wrong.
Now let's count three ways in one query
Let's put three of them in one query, so the difference is impossible to miss. The third one is new on this page, COUNT(DISTINCT ...).
SELECT COUNT(*) AS COUNT_STAR, COUNT(AMOUNT) AS COUNT_AMOUNT, COUNT(DISTINCT CUSTOMER_ID) AS COUNT_DISTINCT_CUST FROM ORDERS;| COUNT_STAR | COUNT_AMOUNT | COUNT_DISTINCT_CUST |
|---|---|---|
| 41 | 40 | 8 |
Three numbers, one table, one row of output, no errors. COUNT(DISTINCT CUSTOMER_ID) looks at the customer column, throws the duplicates away, and counts what is left. So those orders were placed by 8 different customers.
That last one is usually the more interesting number. It is the difference between how much business we did and how many people we did it with, and no amount of staring at the first two columns will give it to us.
The three together are the whole episode. Three correct answers, from the same rows, to three different questions.
Let's try the same thing on EMPLOYEES
Nothing new in this query. It is the same three ideas on the eight-row EMPLOYEES table from Level 1, where we can check every number against rows we already know.
SELECT COUNT(*) AS ALL_ROWS, COUNT(DEPT_ID) AS WITH_DEPT, COUNT(COMMISSION_PCT) AS WITH_COMMISSION FROM EMPLOYEES;| ALL_ROWS | WITH_DEPT | WITH_COMMISSION |
|---|---|---|
| 8 | 7 | 3 |
COUNT(*) returns 8, which is every employee. COUNT(DEPT_ID) returns 7, because Grace has no department. COUNT(COMMISSION_PCT) returns 3, because only three people have a commission percentage at all.
Three numbers, one table, no errors. And if we picked the wrong one, nothing anywhere would tell us. That is worth sitting with for a moment. An aggregate that answers the wrong question looks exactly like an aggregate that answers the right one.
Can DISTINCT mean two different things?
In episode 9, SELECT DISTINCT DEPT_ID returned four rows. The three real departments, and a row for the NULL. Let's ask the counting version of the same question.
SELECT COUNT(DISTINCT DEPT_ID) AS DISTINCT_DEPTS FROM EMPLOYEES;| DISTINCT_DEPTS |
|---|
| 3 |
3, not 4. Both statements have the word DISTINCT in them, they disagree about the answer, and both of them are behaving correctly:
| Statement | What it returns | Why |
|---|---|---|
SELECT DISTINCT DEPT_ID | 4 rows | It returns rows, and a row with nothing in it is still a row |
COUNT(DISTINCT DEPT_ID) | 3 | COUNT discards NULLs before it counts, and DISTINCT inside it does not change that |
Same keyword, two different jobs, and we have now seen it from both sides. If we want the NULL counted as a value of its own, we have to say so explicitly. COUNT will never do it for us.
Now let's add a WHERE clause
Nothing about aggregation takes away the clauses we already know. Let's count only the shipped orders.
SELECT COUNT(*) AS SHIPPED FROM ORDERS WHERE STATUS = 'SHIPPED';| SHIPPED |
|---|
| 34 |
34 shipped orders. The WHERE clause filtered the rows first, and then COUNT counted whatever survived.
That ordering matters more than it looks. WHERE runs before the aggregate, which means WHERE can never filter on an aggregate. The number does not exist yet when WHERE is evaluated. That single fact is the entire reason HAVING exists, which is episode 17.
One last thing: COUNT(1) against COUNT(*)
Sooner or later somebody will tell you to write COUNT(1) instead of COUNT(*). Let's put them in the same query, on the same rows.
SELECT COUNT(1) AS COUNT_ONE, COUNT(*) AS COUNT_STAR FROM ORDERS;| COUNT_ONE | COUNT_STAR |
|---|---|
| 41 | 41 |
Identical. That settles the half of the argument that is about the answer.
The half people actually argue about is which one is faster, and this course is not going to tell you, because it has not been measured. Not on this warehouse and not on any other. Saying so plainly is better than repeating something read somewhere else.
Write COUNT(*). It is what the documentation uses, it is what most people read most easily, and the answer is the same either way. If you later measure a difference on your own warehouse, that measurement beats anybody's opinion — including this one.
Key points
- Every aggregate in Level 2 changes the shape of the answer: many rows go in, one row comes out.
COUNT(*)counts rows. It never looks inside them and never cares whether a column has a value.COUNT(column)counts the rows where that column has a value, so a singleNULLmakes it smaller thanCOUNT(*).COUNT(DISTINCT column)counts how many different values there are — often the question that was actually being asked.SELECT DISTINCTkeeps theNULLas a row;COUNT(DISTINCT ...)discards it first. Same keyword, two jobs.WHEREfilters rows before the aggregate runs, which is why it cannot filter on an aggregate — that is whatHAVINGis for.COUNT(1)andCOUNT(*)return the same number. Which is faster is not claimed here, because it was not measured.- Three counts on one table can all be correct and all be different. Nothing errors, so the only protection is knowing which question you asked.
In the next post we will cover SUM, AVG, MIN and MAX. They have the same shape as COUNT, and they bring the number that settles whether an average divides by 40 or by 41. We will also see what SUM returns when nothing matches at all.
Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the setup script above. The ORDERS and EMPLOYEES data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series — including about COUNT(1) against COUNT(*). Function behaviour follows the Snowflake COUNT documentation.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.