Aggregates (with the exception of COUNT(*)) ignore nulls in all computations.
Ignoring nulls can cause apparent nontransitive anomalies. For example, if there are nulls in column A or column B (or both), the following expression is true unless the values for columns A and B are both null in the same rows. In those cases, the entire row is disregarded in the aggregation.
SUM(A) + SUM(B) <> SUM(A+B)
That is, if and only if field A and field B are both null for every occurrence of a null in either field is the preceding inequality false.
For examples of this behavior, see "Example: Employees Returned as Nulls" and "Example: Counting Employees Not Yet Assigned to a Department" in COUNT Result Type and Attributes. The aggregates are behaving as expected, 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)).