group-by rollup

Syntax

1group data_stream by rollup(fields);

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.

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.

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.

rollup with Null Values and case Statements

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';