Also NULL: SUM(expr) only includes rows for which the expression is not NULL. If there is at least one such row, the result is not NULL, otherwise it is.
Originally Posted by ahmedwaseem2000
With AVG(expr) it's even more apparent: the denominator will be the number of rows for which expr is not NULL.
Actually, the same holds for any of the other aggregate functions:
COUNT(expr) only counts the number of not NULL expr's.
In that respect, COUNT(*) is actually equivalent to COUNT(1).
MIN(expr) and MAX(expr) equally ignore NULL expressions.
All (except for COUNT(expr) of course) return NULL when applied on an empty set, i.e., either an empty table (after having applied the WHERE condition), or an empty list (after removal of the NULL expressions).
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting