15.00 - Nulls and Aggregate Functions - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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).

  • 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))