Share this page

Aggregates in Derivation Formulas

You can use aggregate functions in derivation formulas for simple fields, but not in derivation formulas for table field columns. To derive a value for a simple field based on an aggregate of a table‑field column, use the following syntax:

aggfunction (tablefieldname[*].columnname)

This table lists the aggregates that can be used in derivation formulas, the data types that can be used with each aggregate, and the data type of the derived field:

Aggregate | Data type of source field: | Data type of derived field: |

count | any | integer |

sum | integer float money date (intervals only) | same as source |

avg | integer float money date (intervals only) | same as source except for integer |

max | any | same as source |

min | any | same as source |

If a derivation formula contains a reference to an aggregate of a table field column, and the table field contains invalid values or is empty (has no values), the value of the aggregate is:

• Zero (0), if the aggregate is count

• Null, if the aggregate is avg, sum, max, or min

If the formula contains references to other fields that contain invalid values, VIFRED blanks out the derived field.