Aggregate Functions

About the Aggregate Functions

Thee aggregate functions let you select a group of records from an arbitrary object and produce an aggregate value from them, without having to write Java code. The aggregate value can be sum of values, an average, maximum, or minimum value, or it can be a simple count of records.

Tip: A Rollup Summary Field is often more useful than an aggregate function. With a Rollup Summary Field you define the same kinds of summary calculations for a set of related records--for example, to sum the line items in an order--and store that value as a field in the record, where it is kept up to date whenever the set of related records is modified.

The aggregate functions are available for use in:

• Default Field Values
• External Lookup#Lookup Service Configuration
• External Lookup#Search Service Configuration
• Layout Rule Conditions
• Layout Rule assign-value-to-field action
• Formula Fields
• Validation Criteria

They are not available for use in other contexts in which a Formula Expression may be created.

The Aggregate Functions: SUM, AVG, MAXIMUM, MINIMUM, COUNT

Methods	Field Types
SUM('objectName', 'fieldName', 'criteria_string')	number, currency, boolean
AVG('objectName', 'fieldName', 'criteria_string')	number, currency, boolean
MAXIMUM('objectName', 'fieldName', 'criteria_string')	number, currency, date
MINIMUM('objectName', 'fieldName', 'criteria_string')	number, currency, date
COUNT('objectName', 'criteria_string')	n/a

where:

• 'objectName'

A string containing the object name (not its display label) or the object ID.

For example: 'Some_Object'

• 'fieldName'

A string containing the name of a field in the specified object.

Must be the field name, not its display label. For example: 'some_field'.

Must be a simple field of type number, currency, or boolean.

Does not work for formula fields, text fields, Lookups, or any other kind of field.

• 'criteria_string'

A condition that specifies the object records that are part of the collection, where the expression is contained in a string.

Typically, the criteria selects some field in the object record and compares it to a value in the current record. For example, to get total Orders for different sections of the country, you might have a Section object that contains one record for each state. (That arrangement would let you shift a state from one section to another, at will.) You might then use an aggregate function to total orders for each record:

SUM('Order_Items', 'item_total', 'customer_state =' + section_state)

You could then write a report to group the records by section name, giving you totals for each section of the country.

Important: The results do not take into account user-security restrictions. These functions operate on the entire set of records in an Object, regardless of user capabilities.

Considerations

• The SUM() of a boolean field gives a count of values that are yes/true.
• The AVG() of a boolean field gives the percentage that are yes/true.
• The criteria expression must contain at least one index field.
• If the index is a combination of fields, the criteria expression must contain the first field in that index. (It does not need to be a unique index, but an index is required, for performance.)

Note: An object can have up to five indexes.

• Unlike criteria specified elsewhere in the platform, the criteria expression provided here is in the form of a string.
  Therefore:

1. To test a boolean field, concatenate the value to a string--because in Java, anything concatenated to a string is converted to a string.

   For example: '' + boolean_field

   or: boolean_field + ' AND customer_state = ' + section_state

2. To include a string literal in a criteria string, use two single-quotes for the literal, and enclose the whole expression in single quotes. (The doubling is an escape that produces a single quote within the string.)

   For example: 'string_field = ''some value'' AND customer_state = ' + section_state

To learn about other operators you can use in expressions, see Filter Expressions in APIs.
(But keep the principles above in mind.)