Excluding null values from an aggregate calculation

When calculating the sum of a numeric field, it does not matter if some of the rows contain null values for the specified numeric field. The results are the same, regardless of whether the calculation is 100 + 75 + 200 or 100 + 75 + 0 (null) + 200. In both cases, the result is 375. Note that null is not the same as zero (0). Zero is an actual value, whereas null means there is no value.

Some aggregate calculations, however, return different results when null values are included or excluded from the calculation. The average value returned by the calculation without the null value in the previous example is 125, which is calculated as (100 + 75 + 200)/3. The average value of the calculation with the null value, however, is 93.75, which is calculated as (100 + 75 + 0 + 200)/4. Similarly, COUNT returns a different number of total rows, depending on whether you include or exclude rows with null values for a specified field.

By default, aggregate functions include all rows in their calculations. To exclude rows in which a specified field contains null values, specify a filter condition, as described in the following examples:

If averaging the values in a transactionAmount field, adding the following filter condition performs the aggregate calculation on rows where the transaction amount is not null:

If counting the number of new customers in a report, adding the following filter condition counts only rows in which the creditLimit field has no value (indicating new customers):