Group By Rollup

AVAILABLE API VERSION
API v58.0 and later

The ROLLUP type adds subtotals for aggregate data in the query result. Use ROLLUP with aggregate functions, such as sum and count.

Aggregate Results with Group By Rollup

To aggregate results and include subtotals in the query result, use the following syntax. Replace objectApiName with the object’s API name, like Account. Field1 and Field2 represent fields on the object, like Industry and AnnualRevenue.

1query rollupExample {
2  uiapi {
3    aggregate {
4      objectApiName (groupBy: { type: ROLLUP, Field1: { group: true }, Field2: { group: true } }) {
5        edges {
6          node {
7            aggregate {
8              _Field1_ {
9                value
10                grouping {
11                  value
12                }
13              }
14              _Field2_ {
15                value
16                grouping {
17                  value
18                }
19              }
20              // Other optional fields
21             }
22          }
23        }
24    }
25  }
26}

A query that uses the groupBy argument with the ROLLUP type returns the same aggregated data as a query with the groupBy argument. However, a query with the ROLLUP type also returns multiple levels of subtotals. For example, you can return the number of records for each lead sources and a subtotal of those records using the ROLLUP type.

A query with the ROLLUP type can include up to three fields in a comma-separated list in the groupBy argument clause. The result includes one grand total regardless of how many rollup fields you use.

Note

The order of rollup fields you pass in to the groupBy argument determines how the subtotals are calculated and how the results are grouped.

Group By One Rollup Field

Grouping by one rollup field returns subtotals for each field selection and one grand total. For example, if you query the rollup by LeadSource, the query returns the number of records for each lead source and a grand total.

1query LeadSourceRollupExample {
2  uiapi {
3    aggregate {
4      Lead ( groupBy: { type: ROLLUP, LeadSource: { group: true } } ) {
5        edges {
6          node {
7            aggregate {
8              Name {
9                count {
10                  value
11                }
12              }
13            }
14          }
15        }
16      }
17    }
18  }
19}

The previous query is similar to this SOQL statement.

1SELECT LeadSource, COUNT(Name)
2FROM Lead
3GROUP BY ROLLUP(LeadSource)

Group By Two Rollup Field

A query that includes two rollup fields returns first-level subtotals for Field1. Results are grouped by Field2. For example, if you query the rollup by Status and LeadSource, the query returns subtotals for the number of records for each lead source that has a particular status.

1query LeadSourceExample {
2  uiapi {
3    aggregate {
4      Lead ( groupBy: { type: ROLLUP, Status: { group: true }, LeadSource: { group: true } } ) {
5        edges {
6          node {
7            aggregate {
8              Name {
9                count {
10                  value
11                }
12              }
13            }
14          }
15        }
16      }
17    }
18  }
19}

The previous query is similar to this SOQL statement.

1SELECT Status, LeadSource, COUNT(Name)
2FROM Lead
3GROUP BY ROLLUP(Status, LeadSource)

Group By Three Rollup Field

A query that includes three rollup fields returns:

First-level subtotals for each combination of Field1 and Field2. Results are grouped by Field3.
Second-level subtotals for each value of Field1. Results are grouped by Field2 and Field3.

Group By Rollup

Aggregate Results with Group By Rollup

Group By One Rollup Field

Group By Two Rollup Field

Group By Three Rollup Field

See Also