SOQL and SOSL Reference

Introduction to SOQL and SOSL
Salesforce Object Query Language (SOQL)
Typographical Conventions in This Document
Quoted String Escape Sequences
Reserved Characters
Alias Notation
SOQL SELECT Syntax
SELECT
TYPEOF
USING SCOPE
WHERE
WITH
GROUP BY
GROUP BY ROLLUP
GROUP BY CUBE
Use Aliases with GROUP BY
Identify Subtotals with GROUP BY
Considerations for GROUP BY
HAVING
ORDER BY
LIMIT
OFFSET
FOR VIEW and FOR REFERENCE
UPDATE
FOR UPDATE
SOQL SELECT Examples
SOQL SELECT Functions
Relationship Queries
Change the Batch Size in Queries
SOQL Object Limits and Limitations
Syndication Feed SOQL and Mapping Syntax
Location-Based SOQL Queries
Considerations for Querying Currency Data Using SOQL
Salesforce Object Search Language (SOSL)
PDF

GROUP BY ROLLUP

Use the GROUP BY ROLLUP optional clause in a SOQL query to add subtotals for aggregated data in query results. This action enables the query to calculate subtotals so that you don’t have to maintain that logic in your code.

You can use GROUP BY ROLLUP with aggregate functions, such as SUM() and COUNT(fieldName).

[GROUP BY ROLLUP (fieldName[,...])]

A query with a GROUP BY ROLLUP clause returns the same aggregated data as an equivalent query with a GROUP BY clause. It also returns multiple levels of subtotal rows. You can include up to three fields in a comma-separated list in a GROUP BY ROLLUP clause.

The GROUP BY ROLLUP clause adds subtotals at different levels, aggregating from right to left through the list of grouping columns. The order of rollup fields is important. A query that includes three rollup fields returns the following rows for totals:

  • First-level subtotals for each combination of fieldName1 and fieldName2. Results are grouped by fieldName3.
  • Second-level subtotals for each value of fieldName1. Results are grouped by fieldName2 and fieldName3.
  • One grand total row.
  • You can't combine GROUP BY and GROUP BY ROLLUP syntax in the same statement. For example, GROUP BY ROLLUP(field1), field2 is not valid as all grouped fields must be within the parentheses.
  • If you want to compile a cross-tabular report including subtotals for every possible combination of fields in a GROUP BY clause, use GROUP BY CUBE instead.

Note

Grouping By One Rollup Field

This simple example rolls the results up by one field:

SELECT LeadSource, COUNT(Name) cnt
FROM Lead
GROUP BY ROLLUP(LeadSource)

The following table shows the query results. Note that the aggregated results include an extra row with a null value for LeadSource that gives a grand total for all the groupings. Since there is only one rollup field, there are no other subtotals.

LeadSource cnt
Web 7
Phone Inquiry 4
Partner Referral 4
Purchased List 7
null 22

Grouping By Two Rollup Fields

This example rolls the results up by two fields:

SELECT Status, LeadSource, COUNT(Name) cnt
FROM Lead
GROUP BY ROLLUP(Status, LeadSource)

This table shows the query results that include first-level subtotals and grand total rows. These examples use the COUNT(fieldName) aggregate function, but the syntax works with any aggregate function. You can also group by three rollup fields, which returns even more subtotal rows.

Status LeadSource cnt Comment
Open - Not Contacted Web 1 One lead with Status = Open - Not Contacted and LeadSource = Web
Open - Not Contacted Phone Inquiry 1 One lead with Status = Open - Not Contacted and LeadSource = Phone Inquiry
Open - Not Contacted Purchased List 1 One lead with Status = Open - Not Contacted and LeadSource = Purchased List
Open - Not Contacted null 3 First-level subtotal for all leads with Status = Open - Not Contacted
Working - Contacted Web 4 Four leads with Status = Working - Contacted and LeadSource = Web
Working - Contacted Phone Inquiry 1 One lead with Status = Working - Contacted and LeadSource = Phone Inquiry
Working - Contacted Partner Referral 3 Three leads with Status = Working - Contacted and LeadSource = Partner Referral
Working - Contacted Purchased List 4 Four leads with Status = Working - Contacted and LeadSource = Purchased List
Working - Contacted null 12 First-level subtotal for all leads with Status = Working - Contacted
Closed - Converted Web 1 One lead with Status = Closed - Converted and LeadSource = Web
Closed - Converted Phone Inquiry 1 One lead with Status = Closed - Converted and LeadSource = Phone Inquiry
Closed - Converted Purchased List 1 One lead with Status = Closed - Converted and LeadSource = Purchased List
Closed - Converted null 3 First-level subtotal for all leads with Status = Closed - Converted
Closed - Not Converted Web 1 One lead with Status = Closed - Not Converted and LeadSource = Web
Closed - Not Converted Phone Inquiry 1 One lead with Status = Closed - Not Converted and LeadSource = Phone Inquiry
Closed - Not Converted Partner Referral 1 One lead with Status = Closed - Not Converted and LeadSource = Partner Referral
Closed - Not Converted Purchased List 1 One lead with Status = Closed - Not Converted and LeadSource = Purchased List
Closed - Not Converted null 4 First-level subtotal for all leads with Status = Closed - Not Converted
null null 22 Grand total of 22 leads