AvgNonNull

The AvgNonNull function returns the average (arithmetic mean) of values in a number column, excluding null values.

Avg (numbers, break_col, break_value)

where:

numbers references the column that contains the numbers on which the average is calculated.

break_col is an optional parameter that references a break column.

break_value is an optional parameter that returns the average 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 AvgNonNull function in three separate tables.

Example 1

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

AvgNonNull (Amount)

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

Example 2

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

AvgNonNull (Amount, State)

Table 146. AvgNonNull Example 2

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

Example 3

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

AvgNonNull (Amount, State, ‘CA’)

Table 147. AvgNonNull Example 3

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