COUNT¶

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
    

    Copy

    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);

Copy

SELECT *
    FROM basic_example
    ORDER BY i_col;
+-------+-------+
| I_COL | J_COL |
|-------+-------|
|    11 |   101 |
|    11 |   102 |
|    11 |  NULL |
|    12 |   101 |
|  NULL |   101 |
|  NULL |   102 |
+-------+-------+

Copy

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 |
+----------+--------------+-----------------------+--------------+-----------------------+

Copy

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 |
+-------+----------+--------------+

Copy

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);

Copy

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 |
+------------+

Copy

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);

Copy

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;

Copy

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    |
+-------+-------+-----------------+---------+

Copy

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 |
+----------------+

Copy