count
aggregate function
Applies to: Databricks SQL 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 allexprN
are notNULL
.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