---
schema_version: "1"
id: 3bb6b8937ff4ba1859e7b70b246aa9bdf490f3762c188cb485dc9b51b87e9e40
title: group-by rollup
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/analytics/bi_dev_guide_saql/guides/reference/bi-saql-statement-groupby-rollup.md
canonical_url: https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-statement-groupby-rollup.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: aee985b829f4821582aab6b0ff0cf5d01ada8edb2753cd1762402d0fbd346b9f
products:
  - Analytics SAQL Developer Guide
  - analytics
---

# 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.

```nolang
group data_stream by rollup(fields);
```

| Parameter     | Description                      |
| ------------- | -------------------------------- |
| _data_stream_ | Data input to group.             |
| _fields_      | Fields by which data is grouped. |

**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.

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

| 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.

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

```nolang
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');
```

| 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`.

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

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

**See Also**

- [Null Operators](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-operators-null.md)
- [Simple case Operator](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-operators-case.md)
- [Aggregate Functions](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-functions-aggregate.md)
- [grouping()](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/concept/bi-saql-fn-agg-grouping.md)