Null (SQL) - Aggregate Functions

Aggregate Functions

SQL defines aggregate functions to simplify server-side aggregate calculations on data. Except for the COUNT(*) function, all aggregate functions perform a Null-elimination step, so that Null values are not included in the final result of the calculation.

Note that the elimination of Null values is not equivalent to replacing those values with zero. For example, in the following table, AVG(i) (the average of the values of i) will give a different result from that of AVG(j):

Table
i j
150 150
200 200
250 250
NULL 0

Here AVG(i) is 200 (the average of 150, 200, and 250), while AVG(j) is 150 (the average of 150, 200, 250, and 0). A well-known side effect of this is that in SQL AVG(z) is not equivalent with SUM(z)/COUNT(*).

Read more about this topic:  Null (SQL)

Famous quotes containing the words aggregate and/or functions:

    “a fortress against ideas and against the
    Shuddering insidious shock of the theory-vendors
    The little sardine men crammed in a monster toy
    Who tilt their aggregate beast against our crumbling Troy.”
    —Louis MacNeice (1907–1963)

    “Adolescents, for all their self-involvement, are emerging from the self-centeredness of childhood. Their perception of other people has more depth. They are better equipped at appreciating others’ reasons for action, or the basis of others’ emotions. But this maturity functions in a piecemeal fashion. They show more understanding of their friends, but not of their teachers.”
    —Terri Apter (20th century)