Nulls and Aggregate Functions
With the important exception of COUNT(*), aggregate functions ignore nulls in their arguments. This treatment of nulls is very different from the way arithmetic operators and functions treat them, and is one of the major inconsistencies in the way SQL deals with nulls.
This behavior 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)
In other words, 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.
If this property of nulls presents a problem, you can perform either of the following
workarounds, each of which produces the desired result of the aggregate computation
SUM(A)+SUM(B) = SUM(A+B).
NOT NULL DEFAULT 0.
SUM(ZEROIFNULL(x) + ZEROIFNULL(y))