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:
“We cannot think of a legitimate argument why ... whites and blacks need be affected by the knowledge that an aggregate difference in measured intelligence is genetic instead of environmental.... Given a chance, each clan ... will encounter the world with confidence in its own worth and, most importantly, will be unconcerned about comparing its accomplishments line-by-line with those of any other clan. This is wise ethnocentricism.”
—Richard Herrnstein (1930–1994)
“The mind is a finer body, and resumes its functions of feeding, digesting, absorbing, excluding, and generating, in a new and ethereal element. Here, in the brain, is all the process of alimentation repeated, in the acquiring, comparing, digesting, and assimilating of experience. Here again is the mystery of generation repeated.”
—Ralph Waldo Emerson (1803–1882)