17.10 - Aggregates and Nulls - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Aggregates (with the exception of COUNT(*)) ignore nulls in all computations.

A UDT column value is null only when you explicitly place a NULL in a column, not when a UDT instance has an attribute that is set to null.

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.

There are several ways to work around this apparent nontransitivity issue if it presents a problem. Either solution provides the same consistent results.
  • 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)).