Return an aggregate result different from the aggregate state
The aggregate result is the value that the user-defined aggregate returns to the calling SQL statement. If the user-defined aggregate does not include a FINAL support function, the database server returns the final aggregate state; that is, it returns the value of the aggregate state after the last aggregate iteration. However, if your UDA needs to return a value whose data type is different from the aggregate state, use a FINAL support function to convert the final aggregate state to the data type that you want to return from the aggregate.
For example, the PERCENT_GTR user-defined aggregate (see The PERCENT_GTR user-defined aggregate) returns the percentage of values
greater than some value as a percentage; that is, as a fixed-point
number in the range 0.00 to 100.00. To handle integer values, the
user-defined aggregate would require an aggregate state that holds
the following values:
- The total number of aggregate arguments greater than 10
- The total number of aggregate arguments
- The value to compare against
However, the aggregate result of PERCENT_GTR is a fixed-point number.
Therefore, you would not want the aggregate to return the final state
to the calling SQL statement. Instead, the FINAL support function
needs to perform the following steps:
- Divide the total number of arguments that are greater than 10 by the total number of arguments and multiply by 100.
- Return the fixed-point quotient, which is the percentage of values greater than 10.
For the complete example of the PERCENT_GTR user-defined aggregate, see The PERCENT_GTR user-defined aggregate.