Calculate Grand Totals and Subtotals with the rollup Modifier and grouping() Function

Invoking rollup adds rows to your query results with null values for dimensions and subtotaled results for measures. Invoking grouping() returns 1 if null dimension values are due to higher-level aggregates (which usually means the row is a subtotal), otherwise it returns 0.

Using grouping() alongside rollup lets you work with subtotaled data. After subtotaling data, common next steps include logically evaluating subtotaled data with a case statement. Or filtering on subtotaled data with a filter statement.

Suppose that you have an opportunity dataset, and want to see the value of deals by lead source and type. Plus, you want to see the total value of all lead sources and all types. Write a query that returns the sum of opportunity amount grouped by type and lead source. To see the value of all lead sources and all types, use rollup to subtotal opportunities, then use grouping() to label the subtotaled rows.

Example: rollup

Open the SAQL editor in the dashboard. Instead of grouping data by a field, specify the rollup modifier as the group and pass the fields you want subtotaled - Type and Lead Source - as parameters. Set q = group q by rollup('Type', 'LeadSource');. Here's the full query.

The query results show sum of amount by opportunity type and then by lead source. Subtotaled and grand totaled rows have null values for dimensions.

Example: grouping()

Null values in place of labeled totals can confuse query results. Avoid this confusion by labeling totals as All Types or All Lead Sources using case statements with grouping() functions.

Now the query results include labeled totals.