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.

See also:

MIN / MAX , SUM

Syntax

Aggregate function

COUNT( [ DISTINCT ] <expr1> [ , <expr2> ... ] )

COUNT( * )

Window function

COUNT( <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:

    • The keyword DISTINCT is permitted syntactically, but is ignored.

    • If an ORDER BY sub-clause is used inside the OVER() clause, then a window frame must be used. If no window frame is specified, then the default is a cumulative window frame:

      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

      For more details about window frames, including syntax and examples, see Window Frame Syntax and Usage. For more information about implied window frames, see Window Frame Usage Notes.

Examples

This is an example of using COUNT with NULL values. The query also includes some COUNT(DISTINCT) operations:

CREATE OR REPLACE TABLE count_example(i INT, j INT);

INSERT INTO count_example VALUES
    (11,101), (11,102), (11,NULL), (12,101), (NULL,101), (NULL,102);

SELECT * FROM count_example;

+------+------+
|    I |    J |
|------+------|
|   11 |  101 |
|   11 |  102 |
|   11 | NULL |
|   12 |  101 |
| NULL |  101 |
| NULL |  102 |
+------+------+

SELECT COUNT(*), COUNT(i), COUNT(DISTINCT i), COUNT(j), COUNT(DISTINCT j) FROM count_example;

+----------+----------+-------------------+----------+-------------------+
| COUNT(*) | COUNT(I) | COUNT(DISTINCT I) | COUNT(J) | COUNT(DISTINCT J) |
|----------+----------+-------------------+----------+-------------------|
|        6 |        4 |                 2 |        5 |                 2 |
+----------+----------+-------------------+----------+-------------------+

SELECT i, COUNT(*), COUNT(j) FROM count_example GROUP BY i;

+------+----------+----------+
|    I | COUNT(*) | COUNT(J) |
|------+----------+----------|
|   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 count1 (i_col INTEGER, j_col INTEGER, v VARIANT);
BEGIN WORK;

-- SQL NULL for both a VARIANT column and a non-VARIANT column.
INSERT INTO count1 (i_col, j_col, v) VALUES (NULL, 10, NULL);
-- VARIANT NULL
INSERT INTO count1 (i_col, j_col, v) SELECT 1, 11, PARSE_JSON('{"Title": null}');
-- VARIANT NON-NULL
INSERT INTO count1 (i_col, j_col, v) SELECT 2, 12, PARSE_JSON('{"Title": "O"}');
INSERT INTO count1 (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 count1
    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 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 count1;
+----------------+
| COUNT(V:TITLE) |
|----------------|
|              2 |
+----------------+