With the exception of COUNT(*), aggregate functions ignore nulls in their arguments. This treatment of nulls is different from the way arithmetic operators and functions treat nulls, and is a major inconsistency in the way SQL deals with nulls.
This behavior can cause apparent nontransitive anomalies. For example, if there are nulls in column A, column B, or both, the following expression is typically true.
SUM(A) + (SUM B) <> SUM (A+B)
That is, for the case of SUM, the result is never a simple iterated addition if there are nulls in the data being summed.
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 not counted in the aggregation. This is a trivial case that does not violate the general rule.
The same is true, the necessary changes being made, for all the aggregate functions except COUNT(*), which does include nulls in its result.
- Define all NUMERIC columns as NOT NULL DEFAULT 0.
- Use the ZEROIFNULL function nested within the aggregate function to convert any nulls to zeros for the computation, for example
SUM(ZEROIFNULL(x) + ZEROIFNULL(y))