Categories:

Aggregate Functions (General) , Window Functions (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

COUNT( [ DISTINCT ] <expr1> [ , <expr2> ] ) [ OVER ( [ PARTITION BY <expr3> ] [ ORDER BY <expr4> [ <window_frame> ] ] ) ]

COUNT( * )

Arguments

expr1

This should be either:

  • A column name, which may 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 may include additional column name(s) if you wish. For example, you could list 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

  • 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, since that’s the number of distinct combinations of values in the 3 columns.

  • COUNT( expr ) and COUNT( * ) can be called as a window function (i.e. by specifying an OVER clause) and the window can include an optional window_frame.

    The window_frame (either cumulative or sliding) specifies the subset of rows within the window for which the summed values are returned. If no window_frame is specified, the default is the following cumulative window frame (in accordance with the ANSI standard for window functions):

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    For more details about window frames, including syntax and examples, see Window Frames.

  • 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.

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