Aggregates (with the exception of COUNT(*)) ignore nulls in all computations.
Ignoring nulls can result in apparent nontransitive anomalies. For example, if there are nulls in either column A or column B (or both), then the following expression is virtually always true.
SUM(A) + SUM(B) <> SUM(A+B)
The only exception to this is the case in which the values for columns A and B are both null in the same rows, because in those cases the entire row is disregarded in the aggregation. This is a trivial case that does not violate the general rule.
More formally stated, if and only if field A and field B are both null for every occurrence of a null in either field is the above inequality false.
For examples that illustrate this behavior, see "Example: Employees Returned as Nulls" and "Example: Counting Employees Not Yet Assigned to a Department" in Result Type and Attributes . Note that the aggregates are behaving exactly as they should, the results are not mathematically anomalous.
- Always define your numeric columns as NOT NULL DEFAULT 0.
- Use the ZEROIFNULL function within the aggregate function to convert any nulls to zeros for the computation, for example SUM(ZEROIFNULL(x) + ZEROIFNULL(y)), which produces the same result as SUM(ZEROIFNULL(x)) + SUM(ZEROIFNULL(y)).