SELECT knowledge FROM sql_resources WHERE category='bigquery-standard-sql' AND slug='count-distinct'
COUNT [DISTINCT]
Definition
The COUNT
function returns the number of rows in a SQL expression.
COUNT(*)
counts the number of rows in the input.COUNT(expression)
returns the number of rows in the expression other thanNULL
.
count_all_rows | count_x | count_all_unique_values_of_x |
---|---|---|
5 | 4 | 3 |
The example above shows three different ways COUNT
can be used.
Loading code...
COUNT(*)
counts the number of rows in the table x.COUNT(x)
counts the number of elements in the table x excludingNULL
values.COUNT(DISTINCT x)
counts the number of unique elements in the table x, excludingNULL
values.
COUNT
can also be used as a window function. The below example runs the count function for each value of x.
Loading code...
x | count_all_rows | count_x | count_unique_x |
---|---|---|---|
null | 1 | 0 | 0 |
1 | 1 | 1 | 1 |
2 | 2 | 2 | 1 |
2 | 2 | 2 | 1 |
5 | 1 | 1 | 1 |
Practical Info
expression
can be any data type, but ifDISTINCT
is used then the data type needs to be groupable (all types other thanARRAY
,STRUCT
andGEOGRAPHY
).- The data type of the output is
INT64
.
Related Pages
- Window Functions Explained
Loading code...