Aggregate Functions
Aggregate functions in SOQL work much like their SQL counterparts. They are applied to fields in the SELECT list. After you include an aggregate function in a query, nonaggregate fields in the same query are not allowed. The six aggregate functions available in SOQL are
AVG—Calculates an average of the values in a numeric field.
COUNT—Counts the values in a numeric, date, or string field, including duplicate values but not nulls. Unlike all other aggregate functions, the argument to COUNT is optional.
COUNT_DISTINCT—Counts the unique values in a numeric, date, or string field, excluding nulls.
MIN—Returns the minimum value in a numeric, date, or string field. The minimum of a string field is the first value when values are sorted alphabetically. If the string is a picklist type, the minimum is the first value in the picklist.
MAX—Returns the maximum value in a numeric, date, or string field. The maximum of a string field is the last value when values are sorted alphabetically. If the string is a picklist type, the maximum is the last value in the picklist.
SUM—Computes the sum of values in a numeric field.
All queries containing aggregate functions return a special Apex object called AggregateResult, except the no-argument form of COUNT, which returns an integer. The AggregateResult object contains the aggregate values calculated by running the query. They have default field names expr0 for the first field, expr1, and so forth. Alternatively, you can provide an alias immediately following the aggregate function column to provide a friendlier label for the value in your code. Aggregate result fields are accessed using the get method.
To get started with aggregate functions in Apex, open Force.com IDE’s Execute Anonymous view and type in and run the code given in Listing 5.1.
Listing 5.1 Returning the Record Count
Integer i = [ SELECT COUNT() FROM Timecard__c ];
System.debug(i);
This code prints the number of records contained in the Timecard__c object to the debug log. The SOQL query returns an integer because it uses the no-argument form of the COUNT aggregate function. In contrast, the example in Listing 5.2 uses the SUM aggregate function and returns an AggregateResult object, with an alias Total specified on the aggregate column. Note that if an alias were not specified, the aggregate column would be named expr0.
AggregateResult r = [ SELECT SUM(Total_Hours__c) Total
FROM Timecard__c ];
System.debug(r.get('Total'));
Note
Normal SOQL governor limits apply to aggregate functions. The number of records used to compute an aggregate result are applied toward the limit on records returned. So although your COUNT query returns a single result record, if it counted more than 50,000 records, your query will fail with an exception. If such a failure is disruptive to your application, make sure you use a WHERE clause to reduce the number of records that are processed in the query. The LIMIT keyword is not allowed in queries with aggregate functions, except for the special form of the COUNT function that has no field argument.