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.

Syntax

group-by rollup takes this syntax.

group data_stream by rollup(fields);

note: rollup works with group-by only. You cannot use it with cogroup.

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.

q = load "Superstore";
q = group q by rollup('Category', 'Sub_Category');
q = order q by ('Category');
q = 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.

rollup with Null Values

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.

q = load "Superstore";
q = group q by rollup('Category', 'Sub_Category');
q = foreach q generate 'Category' as 'Category', 'Sub_Category' as 'Sub_Category', sum('Sales') as 'sum_sales';
q = 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.

rollup with Null Values and case Statements

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.

q = load "Superstore";
q = group q by rollup ('Category', 'Sub_Category');
q = foreach q generate
(case
    when grouping('Category') == 1 then "All Categories"
    else 'Category'
end) as 'Category',
(case
    when grouping('Sub_Category') == 1 then "All Sub-Categories"
    else 'Sub_Category'
end) as 'SubCategory', sum('Sales') as 'sum_sales';

See Also

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