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.

data_stream: Data input to group.
fields: Fields by which data is grouped.

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

Note

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.

Category	Sub-Category	sum_sales
Furniture	Bookcases	114,348
	Chairs	328,237
	Furnishings	91,514
	Tables	206,966
	-	741,064
Office Supplies	Appliances	107,532
	Art	27,119
	Binders	203,413
	Envelopes	16,363
	Fasteners	3,024
	Labels	12,486
	Paper	78,479
	Storage	223,844
	Supplies	46,674
	-	718,934
Technology	Accessories	167,380
	Copiers	149,528
	Machines	189,239
	Phones	329,636
	-	835,783
	-	2,295,781

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.

Note

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

Category	Sub-Category	sum_sales
Furniture	Bookcases	114,348
	Chairs	328,237
	Furnishings	91,514
	Tables	206,966
	-	92
	-	741,156
Office Supplies	Appliances	107,532
	Art	27,119
	Binders	203,413
	Envelopes	16,363
	Fasteners	3,024
	Labels	12,486
	Paper	78,479
	Storage	223,844
	Supplies	46,674
	-	273
	-	719,206
Technology	Accessories	167,380
	Copiers	149,528
	Machines	189,239
	Phones	329,636
	-	259
	-	836,041
-	Computers	113
	Projectors	744
	-	562
		1,420
		2,297,824

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.

Category	Sub-Category	sum_sales
Furniture	Bookcases	114,348
	Chairs	328,237
	Furnishings	91,514
	Tables	206,966
	-	92
	All Sub-Categories	741,156
Office Supplies	Appliances	107,532
	Art	27,119
	Binders	203,413
	Envelopes	16,363
	Fasteners	3,024
	Labels	12,486
	Paper	78,479
	Storage	223,844
	Supplies	46,674
	-	273
	All Sub-Categories	719,206
Technology	Accessories	167,380
	Copiers	149,528
	Machines	189,239
	Phones	329,636
	-	259
	All Sub-Categories	836,041
-	Computers	113
	Projectors	744
	-	562
	All Sub-Categories	1,420
All Categories	All Sub-Categories	2,297,824

Tableau CRM SAQL Developer Guide

group-by rollup

Syntax

rollup with Null Values

rollup with Null Values and case Statements

See Also