group-by rollup

rollup is a subclause of group-by that creates and displays aggregations of grouped data. The output of rollup is based on column order in your query.

group-by rollup takes this syntax.

1group data_stream by rollup(fields);

rollup supports the following aggregate functions.

• average()
• count()
• min()
• max()
• sum()
• unique()

This example first groups the results by Category and Sub-Category, and runs sum('Sales'), an aggregate function on each resulting row. By modifying the group-by clause with rollup, the query “rolls up” the results into subtotals and a grand total.

1q = load "Superstore";
2q = group q by rollup('Category', 'Sub_Category');
3q = order q by ('Category');
4q = foreach q generate 'Category' as 'Category', 'Sub_Category' as 'Sub_Category', sum('Sales') as 'sum_sales';

The query first groups the total sales for each sub-category of a given category. Next, it groups the total sales for a single category. After each category’s total sales is accounted for, the query generates the total sales for all categories.

To return grouped null values in your queries, you must select the null handling for dimensions preference in Setup. See group-by for more information.

This example shows how null values display in query results. The query is the same as the one in the first example.

1q = load "Superstore";
2q = group q by rollup('Category', 'Sub_Category');
3q = foreach q generate 'Category' as 'Category', 'Sub_Category' as 'Sub_Category', sum('Sales') as 'sum_sales';
4q = order q by ('Category', 'Sub_Category');

The query first groups the total sales for each sub-category of a given category. In this example, each category contains a null sub-category. The value of the null sub-category is also included in the total sales for each sub-category.

After the query accounts for all of the named categories—categories that have a value—it displays the sub-categories and total sales for null categories. Finally, the query generates the total sales for all categories.

Use the grouping function and case statements together to label the subtotal and grand total categories. In this example, the first case checks for a null value generated by the rollup in the Category field. If true, then the query labels the field All Categories. The second case checks whether a Sub-Category field is similarly null. If true, then the query labels the field All Sub-Categories.

1q = load "Superstore";
2q = group q by rollup ('Category', 'Sub_Category');
3q = foreach q generate
4(case
5    when grouping('Category') == 1 then "All Categories"
6    else 'Category'
7end) as 'Category',
8(case
9    when grouping('Sub_Category') == 1 then "All Sub-Categories"
10    else 'Sub_Category'
11end) as 'SubCategory', sum('Sales') as 'sum_sales';

See Also

• Null Operators
• Simple case Operator
• Aggregate Functions
• grouping()

j

Overview

Quick Start

Examples

SAQL Reference

SAQL Basic Elements

SAQL Operators

SAQL Statements

arimax

cogroup

fill

filter

foreach

group-by

group-by rollup

join semi and anti

limit

load

offset

order

sample

timeseries

union

SAQL Functions

Query Performance

Previous Versions