The aggregation functions available
vary according to the stage you have opened the SQL builder from. The following
are the basic ones supported by all SQL syntax variants.
The following aggregation functions are supported.
- AVG. Returns the mean average of the values in a column.
For example, if you had six rows with a column containing a price, the six
rows would be added together and divided by six to yield the mean average.
If you specify the DISTINCT qualifier, only distinct values will be averaged;
if the six rows only contained four distinct prices then these four would
be added together and divided by four to produce a mean average.
- COUNT. Counts the number of rows that contain a not-null
value in a column. If you specify the DISTINCT qualifier, only distinct values
will be counted.
- MAX. Returns the maximum value that the rows hold in a particular
column. The DISTINCT qualifier can be selected, but has no effect on this
function.
- MIN. Returns the minimum value that the rows hold in a particular
column. The DISTINCT qualifier can be selected, but has no effect on this
function.
- STDDEV. Returns the standard deviation for a set
of numbers.
- VARIANCE. Returns the variance for a set of numbers.
IBM InfoSphere DataStage and InfoSphere QualityStage, Version 8.5
