Skip to main content

COUNTA Function

Generates the count of non-null rows in a specified column, optionally counted by group. Generated value is of Integer type.

Note

Empty string values are counted. Null values are not counted.

Note

When added to a transformation, this function calculates the number of values in the specified column, as displayed in the current sample. Counts are not applied to the entire dataset until you run the job. If you change your sample or run the job, the computed values for this function are updated. Transformations that change the number of rows in subsequent recipe steps do not affect the value for the already computed instance of COUNTA.

For a version of this function computed over a rolling window of rows, see ROLLINGCOUNTA Function.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

counta(name)

Output: Returns the count of non-empty values in the name column.

Syntax and Arguments

counta(function_col_ref) [group:group_col_ref] [limit:limit_count]

Argument

Required?

Data Type

Description

function_col_ref

Y

string

Name of column to which to apply the function

For more information on the group parameter, see Pivot Transform.

function_col_ref

Name of the column from which to count values based on the grouping.

  • Literal values are not supported as inputs.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?

Data Type

Example Value

Yes

String (column reference)

myValues

Examples

Astuce

For additional examples, see Common Tasks.

Example - Simple row count

This example demonstrates how to count the number of values and non-null values within a group.

Functions:

Item

Description

COUNTA Function

Generates the count of non-null rows in a specified column, optionally counted by group. Generated value is of Integer type.

COUNTDISTINCT Function

Generates the count of distinct values in a specified column, optionally counted by group. Generated value is of Integer type.

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:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

COUNTA(Val)

Parameter: New column name

'fctnCounta'

Apply a COUNTDISTINCT function on the source:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

COUNTDISTINCT(Val)

Parameter: New column name

'fctnCountdistinct'

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