Nulls and Aggregate Functions in Teradata

Except COUNT(*), all aggregate functions ignore nulls in their arguments.

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.

Suggestions:
> Always define NUMERIC columns as NOT NULL DEFAULT 0.
> Use the ZEROIFNULL function within the aggregate function to convert any nulls to zeros

SUM(ZEROIFNULL(x)) +SUM( ZEROIFNULL(y))

which produces the same result as this:

SUM(ZEROIFNULL(x) + ZEROIFNULL(y)).