CountNonNull

The CountNonNull function counts the number of rows in a column that do not contain null values.

CountNonNull (numbers, break_col, break_value)

where:

numbers references the column that contains the numbers on which the count of non‑null values is calculated.

break_col is an optional parameter that references a break column.

break_value is an optional parameter that returns the count of non‑null numbers column where value in break_col equals break_value.

Note:

If constant values in the break_value column are substituted for data items, dates and text strings must be enclosed in single quotes.

The following examples show the results of inserting the CountNonNull function in three separate tables.

Example 1

In this example, the CountNonNull function is used on the numeric column. The results are shown in the Computed column.

CountNonNull (Amount)

Table 155. CountNonNull Example 1

State	City	Amount	Computed
AZ	Tucson	112	8
CA	Burbank	240	8
CA	Glendale	NULL	8
FL	Palmetto	70	8
MD	Laurel	97	8
MI	Detroit	208	8
MN	Eagan	159	8
NY	New York	490	8
NY	Rochester	180	8

Example 2

In this example, the CountNonNull function is used on the numeric column and break_column. The results are shown in the Computed column.

CountNonNull (Amount, State)

Table 156. CountNonNull Example 2

State	City	Amount	Computed
AZ	Tucson	112	1
CA	Burbank	240	1
CA	Glendale	NULL	1
FL	Palmetto	70	1
MD	Laurel	97	1
MI	Detroit	208	1
MN	Eagan	159	1
NY	New York	490	2
NY	Rochester	180	2

Example 3

In this example, the CountNonNull function is used on the numeric column, break_column, and break_value. The results are shown in the Computed column.

CountNonNull (Amount, State, ‘CA’)

Table 157. CountNonNull Example 3

State	City	Amount	Computed
AZ	Tucson	112	1
CA	Burbank	240	1
CA	Glendale	97	1
FL	Palmetto	70	1
MD	Laurel	97	1
MI	Detroit	208	1
MN	Eagan	159	1
NY	New York	490	1
NY	Rochester	180	1