Aggregate Functions in SOQL Queries

When using the SOAP API to query existing Salesforce data, you can include SOQL aggregate functions in the SELECT statements of SOQL queries. The origin places the result from the first function of a query into the expr0 field, the result from the second function of the same query into the expr1 field, and so on. The resulting field types depend on the functions and queried fields. The stage does not generate field header attributes for the fields resulting from aggregate functions. You can only include both aggregate functions and non-aggregated fields in the same SELECT statement when you group by the non-aggregated fields.

The following examples demonstrate some uses of aggregate functions in SOQL queries. Each example reads data from the Account object where the name begins with East.

GROUP BY Clause

You can combine aggregate functions with a GROUP BY clause to compute values for groups of records.

Suppose that for records beginning with East, you want a list of industries along with a count of records, the last modified date, and the minimum number of employees grouped by the Industry field.

You can enter the following query:
SELECT Industry, COUNT(Id), MAX(LastModifiedDate), MIN(NumberOfEmployees) FROM Account 
WHERE Id > '${OFFSET}' AND Name LIKE 'East%' 
GROUP BY Industry
The origin places the results from the query into the following fields:
  • Industry
  • expr0 - Integer field contains the count of records
  • expr1 - Datetime field contains the last modified date
  • expr2 - Integer field contains the minimum number of employees

Field Aliases

You can use field aliases in a query to specify the field names where the origin places function results.

Suppose that in the previous example, you want to place the count of records into the cnt field, the last modified date into the max_modify field, and the minimum number of employees into the min_employees field.

You can enter the following query:
SELECT Industry, COUNT(Id) cnt, MAX(LastModifiedDate) max_modify, MIN(NumberOfEmployees) min_employees FROM Account
WHERE Id > '${OFFSET}' AND Name LIKE 'East%' 
GROUP BY Industry
The origin places the results from the query into the following fields:
  • Industry
  • cnt
  • max_modify
  • min_employees

You cannot specify a SOQL keyword, such as count, as an alias.