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):
| 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)
“Nobody is so constituted as to be able to live everywhere and anywhere; and he who has great duties to perform, which lay claim to all his strength, has, in this respect, a very limited choice. The influence of climate upon the bodily functions ... extends so far, that a blunder in the choice of locality and climate is able not only to alienate a man from his actual duty, but also to withhold it from him altogether, so that he never even comes face to face with it.”
—Friedrich Nietzsche (1844–1900)