3. QUEL Data Types : Nulls : Nulls and Aggregate Functions
 
Share this page                  
Nulls and Aggregate Functions
If you execute 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 distorts the true average age. If a null is assigned to any missing ages, the aggregate returns a correct result: the average of all known employee ages.
Aggregate functions, with the exception of count(), return null for an aggregate over an empty set, even when the aggregate includes columns which are not nullable (count() returns 0).
In the following example, the retrieve returns null, because there are no rows in "test."
create  table  test  (col1=integer  not  null)
retrieve  (x=max(test.col1))
In the above example, you can use the ifnull function to return a zero (0) instead of a null:
retrieve (ifnull(max(test.coll),0))
For more information, see Ifnull.