Grouping Records

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.

Listing 5.3 Returning Unique Records by Grouping Them

Click here to view code image

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.

Listing 5.4 Using Aggregate Functions with Record Groupings

Click here to view code image

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.

Listing 5.5 Filtering Grouped Records by Aggregate Function Values

Click here to view code image

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'));
}