This example demonstrates how to count the number of values and non-null values within a group. |
Functions:
Source:
In the following example, the seventh row is an empty string, and the eighth row is a null value.
rowId | Val |
---|---|
r001 | val1 |
r002 | val1 |
r003 | val1 |
r004 | val2 |
r005 | val2 |
r006 | val3 |
r007 | (empty) |
r008 | (null) |
Transformation:
Apply a COUNTA
function on the source column:
Apply a COUNTDISTINCT
function on the source:
Results:
Below, both functions count the number of values in the column, with COUNTDISTINCT
counting distinct values only. The empty value for r007
is counted by both functions.
rowId | Val | fctnCountdistinct | fctnCounta |
---|---|---|---|
r001 | val1 | 4 | 7 |
r002 | val1 | 4 | 7 |
r003 | val1 | 4 | 7 |
r004 | val2 | 4 | 7 |
r005 | val2 | 4 | 7 |
r006 | val3 | 4 | 7 |
r007 | (empty) | 4 | 7 |
r008 | (null) | 4 | 7 |