Nulls and Aggregate Functions
When executing an aggregate function against a column that contains nulls, the function ignores the nulls. This prevents unknown or inapplicable values from affecting the result of the aggregate.
For example, if you apply the aggregate function, avg(), to a column that holds the ages of your employees, you want to be sure that any ages that have not been entered in the table are not treated as zeros by the function. This would distort the true average age. If a null is assigned to any missing ages, then the aggregate returns a correct result: the average of all known employee ages.
Aggregate functions, except count(), return null for an aggregate over an empty set, even when the aggregate includes columns that are not nullable (in this case, count() returns 0). In the following example, the select returns null, since there are no rows in test:
create table test (col1 integer not null);
select max(col1) as x from test;
When specifying a column that contains nulls as a grouping column (that is, in the group by clause) for an aggregate function, OpenSQL considers all nulls in the column as equal for the purposes of grouping. This is the one exception to the rule that nulls are not equal to other nulls. For information about the group by clause, see The Group By Clause in the “OpenSQL Statements” chapter.
Last modified date: 01/30/2023