Categories:
Aggregate Functions (General) , Window Functions (Window Frame)

COUNT

Returns either the number of non-NULL records for the specified columns, or a 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
A column name.
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 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 |
+----------------+