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
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 CUBE

Use the GROUP BY CUBE clause in a SOQL query to add subtotals for all combinations of a grouped field in the query results. This action is useful for compiling cross-tabular reports of data. For example, you can create a cross-tabular query to calculate a sum, an average, or another aggregate function and then group the results by two sets of values: one horizontally, the other, vertically.

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

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

A query with a GROUP BY CUBE clause returns the same aggregated data as an equivalent query with a GROUP BY clause. It also returns additional subtotal rows for each combination of fields specified in the comma-separated grouping list, as well as a grand total. You can include up to three fields in a GROUP BY CUBE clause.

  • You can't combine GROUP BY and GROUP BY CUBE syntax in the same statement. For example, GROUP BY CUBE(field1), field2 is not valid as all grouped fields must be within the parentheses.
  • If you only want subtotals for a subset of the grouped field combinations, use GROUP BY ROLLUP instead.

Note

The following query returns subtotals of accounts for each combination of Type and BillingCountry:

SELECT Type, BillingCountry,
    GROUPING(Type) grpType, GROUPING(BillingCountry) grpCty,
    COUNT(id) accts
FROM Account
GROUP BY CUBE(Type, BillingCountry)
ORDER BY GROUPING(Type), GROUPING(BillingCountry)

The following table shows the query results. The query uses ORDER BY GROUPING(Type), GROUPING(BillingCountry) so that the subtotal and grand total rows are returned after the aggregated data rows. This is not necessary, but it can help you when you are iterating through the query results in your code.

Type BillingCountry grpType grpCty accts Comment
Customer - Direct null 0 0 6 Six accounts with Type = Customer - Direct with BillingCountry = null
Customer - Channel USA 0 0 1 One account with Type = Customer - Channel with BillingCountry = USA
Customer - Channel null 0 0 2 Two accounts with Type = Customer - Channel with BillingCountry = null
Customer - Direct USA 0 0 1 One account with Type = Customer - Direct with BillingCountry = USA
Customer - Channel France 0 0 1 One account with Type = Customer - Channel with BillingCountry = France
null USA 0 0 1 One account with Type = null with BillingCountry = USA
Customer - Channel null 0 1 4 Subtotal of four accounts with Type = Customer - Channel (grpCty = 1 indicates that result is grouped by the BillingCountry field)
Customer - Direct null 0 1 7 Subtotal of seven accounts with Type = Customer - Direct (grpCty = 1 indicates that result is grouped by the BillingCountry field)
null null 0 1 1 Subtotal of one account with Type = null (grpCty = 1 indicates that result is grouped by the BillingCountry field)
null France 1 0 1 Subtotal of one account with BillingCountry = France (grpType = 1 indicates that result is grouped by the Type field)
null USA 1 0 3 Subtotal of three accounts with BillingCountry = USA (grpType = 1 indicates that result is grouped by the Type field)
null null 1 0 8 Subtotal of eight accounts with BillingCountry = null (grpType = 1 indicates that result is grouped by the Type field)
null null 1 1 12 Grand total of 12 accounts (grpType = 1 and grpCty = 1 indicates this is the grand total)

You can use these query results to present a cross-tabular reports of the results.

Type/BillingCountry USA France null Total
Customer - Direct 1 0 6 7
Customer - Channel 1 1 2 4
null 1 0 0 1
Total 3 1 8 12