Identify Subtotals with GROUP BY
When you use the GROUP BY ROLLUP or GROUP BY CUBE clause in a SOQL query to add the subtotals, you can identify whether the row is a subtotal for a field using the GROUPING(fieldName) function.
If you are iterating through the query result to create a report or chart of the data, you have to distinguish between aggregated data and subtotal rows. You can use GROUPING(fieldName) to do this. Using GROUPING(fieldName) is more important for interpreting your results when you have more than one field in your GROUP BY ROLLUP or GROUP BY CUBE clause. It is the best way to differentiate between aggregated data and subtotals.
This example query returns subtotals for combinations of the LeadSource and Rating fields. GROUPING(LeadSource) indicates if the row is an aggregated row for the LeadSource field, and GROUPING(Rating) does the same for the Rating field.
1SELECT LeadSource, Rating,
2 GROUPING(LeadSource) grpLS, GROUPING(Rating) grpRating,
3 COUNT(Name) cnt
4FROM Lead
5GROUP BY ROLLUP(LeadSource, Rating)
The following table shows the query results.
| LeadSource | Rating | grpLS | grpRating | cnt | Comment |
|---|
Web | null | 0 | 0 | 5 | Five leads with LeadSource = Web with no Rating |
Web | Hot | 0 | 0 | 1 | One lead with LeadSource = Web with Rating = Hot |
Web | Warm | 0 | 0 | 1 | One lead with LeadSource = Web with Rating = Warm |
Web | null | 0 | 1 | 7 | Subtotal of seven leads with LeadSource = Web (grpRating = 1 indicates that result is grouped by the Rating field) |
Phone Inquiry | null | 0 | 0 | 4 | Four leads with LeadSource = Phone Inquiry with no Rating |
Phone Inquiry | null | 0 | 1 | 4 | Subtotal of four leads with LeadSource = Phone Inquiry (grpRating = 1 indicates that result is grouped by the Rating field) |
Partner Referral | null | 0 | 0 | 4 | Four leads with LeadSource = Partner Referral with no Rating |
Partner Referral | null | 0 | 1 | 4 | Subtotal of four leads with LeadSource = Partner Referral (grpRating = 1 indicates that result is grouped by the Rating field) |
Purchased List | null | 0 | 0 | 7 | Seven leads with LeadSource = Purchased List with no Rating |
Purchased List | null | 0 | 1 | 7 | Subtotal of seven leads with LeadSource = Purchased List (grpRating = 1 indicates that result is grouped by the Rating field) |
null | null | 1 | 1 | 22 | Grand total of 22 leads (grpRating = 1 and grpLS = 1 indicates this is the grand total) |
The order of the fields listed in the GROUP BY ROLLUP clause is important. For example, if you are more interested in getting subtotals for each Rating instead of for each LeadSource, switch the field order to GROUP BY ROLLUP(Rating, LeadSource).
j