Aggregate Functions

These aggregate functions give you the ability to select a group of records from an arbitrary object and produce an aggregate value from them, without having to write Java code.

About the Aggregate Functions

The aggregate functions are available for use in:

• Validations
• Default Field Values
• Formula Fields

They are not available for use in:

• Layout Rules
• Custom Access Criteria
• Reports

Tip: (For most purposes, a Rollup Summary Field tends to be more useful, as it lets you define a summary calculation for a set of related records--for example, to sum the line items in an order. The big advantage being that the calculation is automatically updated when line items change.

The Functions

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 double quotes for the literal, and enclose the whole expression in single quotes.

   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.)