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[, 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