- Categories:
Aggregate Functions (General) , Window Functions (General, Window Frame)
COUNT¶
Returns either the number of non-NULL records for the specified columns, or the total number of records.
Syntax¶
Aggregate function
COUNT( [ DISTINCT ] <expr1> [ , <expr2> ... ] )
COUNT( * )
Window function
COUNT( [ DISTINCT ] <expr1> [ , <expr2> ... ] ) OVER (
[ PARTITION BY <expr3> ]
[ ORDER BY <expr4> [ ASC | DESC ] [ <window_frame> ] ]
)
For details about window_frame
syntax, see Window Frame Syntax and Usage.
Arguments¶
expr1
This should be either:
A column name, which can be a qualified name (e.g. database.schema.table.column_name).
Alias.*
, which indicates that the function should return the number of rows that do not contain any NULLs. See Examples for an example.
expr2
You can include additional column name(s) if you wish. For example, you could count the number of distinct combinations of last name and first name.
expr3
The column to partition on, if you want the result to be split into multiple windows.
expr4
The column to order each window on. Note that this is separate from any ORDER BY clause to order the final result set.
Usage Notes¶
This function treats VARIANT NULL (JSON null) as SQL NULL.
For more information about NULL values and aggregate functions, see Aggregate Functions and NULL Values.
When this function is called as an aggregate function:
If the
DISTINCT
keyword is used, it applies to all columns. For example,DISTINCT col1, col2, col3
means to return the number of different combinations of columns col1, col2, and col3. For example, if the data is:1, 1, 1 1, 1, 1 1, 1, 1 1, 1, 2
then the function will return 2, because that’s the number of distinct combinations of values in the 3 columns.
When this function is called as a window function (i.e. with an OVER clause):
If the OVER clause contains an ORDER BY subclause, then:
A window frame is required. If no window frame is specified explicitly, then the ORDER BY implies a cumulative window frame:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
For information about window frames, including syntax and examples, see Window Frame Syntax and Usage.
For information about implied window frames, see also Window Frame Usage Notes.
Using the keyword DISTINCT inside the window function is prohibited and results in a compile-time error.
To return the number of rows that match a condition, use COUNT_IF.
When possible, use the COUNT function on tables and views without a row access policy. The query with this function is faster and more accurate on tables or views without a row access policy. The reasons for the performance difference include:
Snowflake maintains statistics on tables and views, and this optimization allows simple queries to run faster.
When a row access policy is set on a table or view and the COUNT function is used in a query, Snowflake must scan each row and determine whether the user is allowed to view the row.
Examples¶
This is an example of using COUNT
with NULL values. The query also includes some COUNT(DISTINCT)
operations:
CREATE TABLE basic_example (i_col INTEGER, j_col INTEGER); INSERT INTO basic_example VALUES (11,101), (11,102), (11,NULL), (12,101), (NULL,101), (NULL,102);SELECT * FROM basic_example ORDER BY i_col; +-------+-------+ | I_COL | J_COL | |-------+-------| | 11 | 101 | | 11 | 102 | | 11 | NULL | | 12 | 101 | | NULL | 101 | | NULL | 102 | +-------+-------+SELECT COUNT(*), COUNT(i_col), COUNT(DISTINCT i_col), COUNT(j_col), COUNT(DISTINCT j_col) FROM basic_example; +----------+--------------+-----------------------+--------------+-----------------------+ | COUNT(*) | COUNT(I_COL) | COUNT(DISTINCT I_COL) | COUNT(J_COL) | COUNT(DISTINCT J_COL) | |----------+--------------+-----------------------+--------------+-----------------------| | 6 | 4 | 2 | 5 | 2 | +----------+--------------+-----------------------+--------------+-----------------------+SELECT i_col, COUNT(*), COUNT(j_col) FROM basic_example GROUP BY i_col ORDER BY i_col; +-------+----------+--------------+ | I_COL | COUNT(*) | COUNT(J_COL) | |-------+----------+--------------| | 11 | 3 | 2 | | 12 | 1 | 1 | | NULL | 2 | 2 | +-------+----------+--------------+
The following example shows that COUNT(alias.*)
returns the number of rows that do not contain any NULL values.
Create a set of data such that:
1 row has all nulls.
2 rows have exactly one null.
3 rows have at least one null.
There are a total of 4 NULL values.
5 rows have no nulls.
There are a total of 8 rows.
CREATE TABLE non_null_counter(col1 INTEGER, col2 INTEGER); INSERT INTO non_null_counter(col1, col2) VALUES (NULL, NULL), -- all NULL values (NULL, 1), -- one NULL value (1, NULL), -- one NULL value (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);The query returns a count of 5, which is the number of rows that do not contain any NULL values:
SELECT COUNT(n.*) FROM non_null_counter AS n; +------------+ | COUNT(N.*) | |------------| | 5 | +------------+
The following example shows that JSON (VARIANT) NULL is treated as SQL NULL by the COUNT
function.
Create the table and insert data that contains both SQL NULL and JSON NULL values:
CREATE TABLE count_example_with_variant_column (i_col INTEGER, j_col INTEGER, v VARIANT);BEGIN WORK; -- SQL NULL for both a VARIANT column and a non-VARIANT column. INSERT INTO count_example_with_variant_column (i_col, j_col, v) VALUES (NULL, 10, NULL); -- VARIANT NULL (aka JSON null) INSERT INTO count_example_with_variant_column (i_col, j_col, v) SELECT 1, 11, PARSE_JSON('{"Title": null}'); -- VARIANT NON-NULL INSERT INTO count_example_with_variant_column (i_col, j_col, v) SELECT 2, 12, PARSE_JSON('{"Title": "O"}'); INSERT INTO count_example_with_variant_column (i_col, j_col, v) SELECT 3, 12, PARSE_JSON('{"Title": "I"}'); COMMIT WORK;Show the data:
SELECT i_col, j_col, v, v:Title FROM count_example_with_variant_column ORDER BY i_col; +-------+-------+-----------------+---------+ | I_COL | J_COL | V | V:TITLE | |-------+-------+-----------------+---------| | 1 | 11 | { | null | | | | "Title": null | | | | | } | | | 2 | 12 | { | "O" | | | | "Title": "O" | | | | | } | | | 3 | 12 | { | "I" | | | | "Title": "I" | | | | | } | | | NULL | 10 | NULL | NULL | +-------+-------+-----------------+---------+Show that the
COUNT
function treats both the NULL and the VARIANT NULL (JSON null) values as NULLs. There are 4 rows in the table. One has a SQL NULL and the other has a VARIANT NULL. Both those rows are excluded from the count, so the count is 2.SELECT COUNT(v:Title) FROM count_example_with_variant_column; +----------------+ | COUNT(V:TITLE) | |----------------| | 2 | +----------------+