Grouping Records with Subtotals
Two special forms of grouping in SOQL produce subtotals and grand totals for the record groupings specified in the query. They are GROUP BY ROLLUP and GROUP BY CUBE, and they replace GROUP BY syntax and support up to three grouped fields. These functions make it easier for developers to produce cross-tabular or pivot-style outputs common to reporting tools, where groups become the axes and aggregate values are the cells. The Force.com database calculates the totals and provides them in-line, in the results, eliminating the need to write Apex to postprocess the data.
Listing 5.6 demonstrates GROUP BY ROLLUP to add subtotals to combinations of two fields: Status__c and Region__c. Because Status__c appears first in the GROUP BY ROLLUP function, the subtotals are calculated for each of its unique values. The function GROUPING is used to identify subtotal records, and also to order the results so that the subtotals appear last.
Listing 5.6 Subtotals on Two Field Groupings
for (AggregateResult r : [ SELECT Project__r.Status__c, Contact__r.Region__c,
SUM(Total_Hours__c) hours, COUNT(Id) recs,
GROUPING(Project__r.Status__c) status, GROUPING(Contact__r.Region__c) region
FROM Timecard__c
GROUP BY ROLLUP(Project__r.Status__c, Contact__r.Region__c)
ORDER BY GROUPING(Project__r.Status__c), GROUPING(Contact__r.Region__c) ]) {
System.debug(LoggingLevel.INFO,
r.get('Status__c') + ' ' + r.get('Region__c') + ' ' +
r.get('region') + ' ' + r.get('status') + ' ' +
r.get('hours') + ' ' + r.get('recs'));
}
Listing 5.7 shows the result of running the code in Listing 5.6 on a database containing 13 Timecard records spread across West and Central regions’ projects in Yellow and Green status. Note the third and fourth columns contain the value of the GROUPING function. Here, a 1 indicates that the record is a subtotal, and 0 indicates a normal record. For example, the fifth record from the top is a subtotal on status because the 1 appears in the status column. The other values in that record indicate the sum of all Timecard hours for projects in Yellow status is 109, and that this constitutes three records’ worth of data. The final record contains the grand totals, which you can verify by adding the record count of the Green subtotal (10) to the Yellow subtotal (3).
Listing 5.7 Excerpt of Debug Log after Running Code in Listing 5.6
16:04:43.207|USER_DEBUG|[7]|INFO|Green West 0 0 230.0 6
16:04:43.207|USER_DEBUG|[7]|INFO|Green Central 0 0 152.0 4
16:04:43.207|USER_DEBUG|[7]|INFO|Yellow Central 0 0 109.0 3
16:04:43.207|USER_DEBUG|[7]|INFO|Green null 1 0 382.0 10
16:04:43.207|USER_DEBUG|[7]|INFO|Yellow null 1 0 109.0 3
16:04:43.207|USER_DEBUG|[7]|INFO|null null 1 1 491.0 13
To experiment with GROUP BY CUBE, replace the word ROLLUP with CUBE in Listing 5.6 and run the code. The GROUP BY CUBE syntax causes all possible combinations of grouped fields to receive subtotals. The results are shown in Listing 5.8. Note the addition of two records, subtotals on the Region__c field indicated by a 1 in the region column.
Listing 5.8 Excerpt of Debug Log after Changing Listing 5.6 to Group By Cube
16:06:56.003|USER_DEBUG|[7]|INFO|Green Central 0 0 152.0 4
16:06:56.003|USER_DEBUG|[7]|INFO|Green West 0 0 230.0 6
16:06:56.004|USER_DEBUG|[7]|INFO|Yellow Central 0 0 109.0 3
16:06:56.004|USER_DEBUG|[7]|INFO|Green null 1 0 382.0 10
16:06:56.004|USER_DEBUG|[7]|INFO|Yellow null 1 0 109.0 3
16:06:56.004|USER_DEBUG|[7]|INFO|null West 0 1 230.0 6
16:06:56.004|USER_DEBUG|[7]|INFO|null Central 0 1 261.0 7
16:06:56.005|USER_DEBUG|[7]|INFO|null null 1 1 491.0 13