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.
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).
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.
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
18.224.54.136