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[, expr...] ) [FILTER ( WHERE cond ) ]

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

Arguments

  • expr: Any expression.

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

Returns

A BIGINT.

If * is specified also counts row containing NULL values.

If expr are specified counts only rows for which all expr are not NULL.

If DISTINCT duplicate rows are not counted.

Examples 

> SELECT count(*) 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