Calculates a single aggregate value for a collection of rows on the database and assigns it to a variable. Because the operation executes server-side, you may see a performance improvement compared to doing the same operation client-side using the ACCUMULATE statement and ACCUM function.

Syntax

AGGREGATE aggregate-target-var = { aggregate-operation } (field) 
  FOR table-name [ where-clause ].
aggregate-target-var
The name of a field or variable to set with the aggregate value. The value returned is numeric, therefore a DECIMAL variable holds any value returned. You may use an INTEGER or INT64 variable, but the value returned is rounded.
aggregate-operation
Valid values are COUNT, TOTAL and AVERAGE.
  • COUNT - Counts the number of rows of field that satisfy the where-clause, if specified. Unknown (?) values are included in the count.
  • TOTAL - Sums the values of field in all rows that satisfy the where-clause, if specified. field must be a field of some numeric data type. Unknown (?) values are not included in the total.
  • AVERAGE - Calculates the average of all values of the field that satisfy the where-clause, if specified. field must be a field of some numeric data type. When computing the average (total/count), unknown (?) values are included in the count, but not the total. If the query returns no records for the AVERAGE calculation, the AGGREGATE statement returns the Unknown (?) value.
field
The field name of a table you want to get the aggregate for. The field name can be qualified with the table name (for example, TableName.FieldName).
FOR table-name [ where-clause ]
Defines the table name of the field and an optional where-clause for limiting the range of records to be used for the aggregate. If no where-clause is specified, then all values are included.

If field is also qualified with a table name, then the table buffers must be the same.

Examples

This example code counts the number of records in the Customer table:
VAR INTEGER numCustomers.

AGGREGATE numCustomers = COUNT(CustNum) FOR Customer.
  
MESSAGE "Number of customers: " numCustomers
  VIEW-AS ALERT-BOX.
This example code counts the number of customers in Boston:
VAR INTEGER numCustomers.

AGGREGATE numCustomers = Count(CustNum) FOR Customer 
  WHERE Country EQ 'USA' AND City EQ 'Boston'.

MESSAGE "Number of customers: " numCustomers
  VIEW-AS ALERT-BOX.
This example code calculate the average balance for customers in Chicago:
VAR DECIMAL avgBalance.

AGGREGATE avgBalance = AVERAGE(Balance) FOR Customer
  WHERE Country EQ 'USA' AND City EQ 'Chicago'.

MESSAGE "Average balance: " avgBalance
  VIEW-AS ALERT-BOX.
This example code sums the customer balances for customers in Los Angeles:
VAR DECIMAL totalBalance.

AGGREGATE totalBalance = TOTAL(Balance) FOR Customer
  WHERE Country EQ 'USA' AND City EQ 'Los Angeles'.

MESSAGE "Total balance: " totalBalance
  VIEW-AS ALERT-BOX.

Notes

  • The AGGREGATE statement accesses all records using NO-LOCK.
  • The AGGREGATE statement cannot be inside a FOR loop for the same buffer.
  • If the session has a record in scope, then that record is no longer in scope after the AGGREGATE statement executes. Therefore, you should not use the same buffers in AGGREGATE statements that are used in other parts of the application.
  • The AGGREGATE statement is not limited to network clients. It can also be used with self-service clients.
  • Query information logging is available for the AGGREGATE statement. Information is logged at level 3 (Verbose).
  • The following limitations apply to the AGGREGATE statement:
    • Can only be used with a single aggregate on a single table.
    • Is not supported with the TENANT-WHERE clause for multi-tenant tables.