count aggregate function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the number of retrieved rows in a group.

Syntax 

count ( [DISTINCT | ALL] * ) [FILTER ( WHERE cond ) ]

count ( [DISTINCT | ALL] expr [, ...] ) [FILTER ( WHERE cond ) ]

This function can also be invoked as a window function using the OVER clause.

Arguments

  • *: Counts all rows in the group.

  • expr: Counts all rows for which all exprN are not NULL.

  • cond: An optional boolean expression filtering the rows used for aggregation.

Returns

A BIGINT.

If DISTINCT is specified then the function returns the number of unique values which do not contain NULL.

If ALL is specified then the function returns the number of all values. In case of * this includes those containing NULL.

Examples 

> SELECT count(*) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
 4

> SELECT count(1) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
 4

> SELECT count(col) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
 3

> SELECT count(col) FILTER(WHERE col < 10)
    FROM VALUES (NULL), (5), (5), (20) AS tab(col);
 2

> SELECT count(DISTINCT col) FROM VALUES (NULL), (5), (5), (10) AS tab(col);
 2

> SELECT count(col1, col2)
    FROM VALUES (NULL, NULL), (5, NULL), (5, 1), (5, 2), (5, 2), (NULL, 2), (20, 2) AS tab(col1, col2);
 4

> SELECT count(DISTINCT col1, col2)
    FROM VALUES (NULL, NULL), (5, NULL), (5, 1), (5, 2), (NULL, 2), (20, 2) AS tab(col1, col2);
 3