Force.com SOQL and SOSL Reference

Clouds with binary code floating aboveCloud with binary code floating above

No Results

Search Tips:

  • Please consider misspellings
  • Try different search keywords
PDF

Newer Version Available

This content describes an older version of this product. View Latest

Using GROUPING(fieldName) to Identify Subtotals

You can use the GROUPING(fieldName) function to determine whether a row is a subtotal or field when you use GROUP BY ROLLUP or GROUP BY CUBE in SOQL queries.

The GROUP BY ROLLUP or GROUP BY CUBE clause adds the subtotals, and then the GROUPING(fieldName) function identifies whether the row is a subtotal for a field.

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.

GROUPING(fieldName) returns 1 if the row is a subtotal for the field, and 0 otherwise. You can use GROUPING(fieldName) in SELECT, HAVING, and ORDER BY clauses.

The easiest way to understand more is to look at a query and its results.

1swfobject.registerObject("clippy.codeblock-0", "9");SELECT LeadSource, Rating,
2    GROUPING(LeadSource) grpLS, GROUPING(Rating) grpRating,
3    COUNT(Name) cnt
4FROM Lead
5GROUP BY ROLLUP(LeadSource, Rating)

The 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.

The following table shows the query results. The Comment column explains each row.

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

Tip