SOQL provides the GROUP BY
syntax for grouping records by one or more fields. When a query contains a grouping, its results are collapsed into a single record for each unique value in the grouped field. Because you can no longer return individual field values, all fields not specified as grouped must be placed within aggregate functions.
Listing 5.3 shows a simple example of grouping records without aggregate functions. It examines all the records in the Contact object and returns only the unique values of the field Region__c
.
for (AggregateResult r : [ SELECT Region__c FROM Contact
GROUP BY Region__c ]) {
System.debug(r.get('Region__c'));
}
Although aggregate functions can be used alone in a simple query, they are much more powerful when used in conjunction with record groupings. Listing 5.4 demonstrates aggregate functions with record groupings. It groups all Timecard records by the geographic region of the consultant (Contact) who performed the work, and sums their reported hours. This results in one record per geographic region with the region’s name and a sum of their timecard hours.
for (AggregateResult r : [ SELECT Contact__r.Region__c,
SUM(Total_Hours__c) FROM Timecard__c
GROUP BY Contact__r.Region__c ]) {
System.debug(r.get('Region__c') + ' ' + r.get('expr0'));
}
You’re already familiar with the WHERE
keyword in SOQL for filtering query results using Boolean expressions. Filtering on the results of aggregate functions requires the HAVING
keyword. It works just like WHERE
, but the field being filtered must be wrapped with an aggregate function and included in the GROUP BY
list.
The code in Listing 5.5 outputs the average hourly cost rates for consultants by education level, but excludes records at or below an average cost rate of $100. The filtering of the average cost rates is specified by the HAVING
keyword.
for (AggregateResult r : [ SELECT Highest_Education_Level__c ed,
AVG(Hourly_Cost_Rate__c) FROM Contact
GROUP BY Highest_Education_Level__c
HAVING AVG(Hourly_Cost_Rate__c) > 100 ]) {
System.debug(r.get('ed') + ' ' + r.get('expr0'));
}
3.144.237.154