--- schema_version: "1" id: a4788b66dc54a55f905bd4d6b08c3a6d6a4d73ced2a58c158c01e7f2927a0bd1 title: GROUP BY Clause collection: developer version: current locale: en-us portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/sql-commands/select-group-by.md canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-group-by.html source_site: developer.salesforce.com content_type: documentation content_hash: f2b32ad24a068fe1b720f942c8215a92ef851aba752ceea0b8890d867ebbfc60 products: - Data 360 Query Guide - Data 360 Developer Documentation --- # `GROUP BY` Clause Use `GROUP BY` to combine rows that have the same values. Aggregate functions then calculate one result for each group. See [Aggregate Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/aggregate-func/aggregate.md). ## Syntax ```sql SELECT [ALL | DISTINCT [ON ( [, ...])]] ... [GROUP BY { ALL | [ALL | DISTINCT] [, ...] }] ... ``` `grouping_element` can be one of: ```sql () (s [, ...]) ROLLUP ({ | ( [, ...])} [, ...]) CUBE ({ | ( [, ...])} [, ...]) GROUPING SETS ( [, ...]) ``` ## Choose Values to Group By For each ``, use one of these values. - **Input column name:** Groups rows by values in a source column. - **Output column name:** Groups rows by a named column in the `SELECT` list. - **Output column position:** Groups rows by the column's left-to-right position in the `SELECT` list. - **Expression based on input columns:** Groups rows by a calculated value. - **Empty grouping set `()`:** Combines all rows into one group. If a name matches both an input column and an output column, `GROUP BY` uses the input column. When you use `GROUP BY` or an aggregate function, every selected column must be either: - Included in the grouping values. - Used in an aggregate function. Data 360 SQL calculates aggregate functions before it calculates scalar expressions in the `HAVING` clause or `SELECT` list. `ALL` has two meanings in a `GROUP BY` clause. - `GROUP BY ALL` with no grouping list automatically groups on the non-aggregate values in the `SELECT` list. - `ALL` before an explicit grouping list retains duplicate grouping sets. This behavior is the default. Use `DISTINCT` to remove duplicate grouping sets. ## `GROUP BY ALL` Use `GROUP BY ALL` to group on every non-aggregate value in the `SELECT` list without repeating those values in the `GROUP BY` clause. An aggregate function, such as `sum()` or `count()`, calculates one value for each group. `GROUP BY ALL` doesn't group on aggregate results. `GROUP BY ALL` follows these rules. - **Only aggregate functions selected:** All rows form one group, the same as with `GROUP BY ()`. - **No aggregate functions selected:** The query groups on every selected value. The result contains the same rows as `SELECT DISTINCT` for those values. - **Expression selected:** The complete expression is one grouping value. For example, `a * b` is one grouping value, not separate groups for `a` and `b`. - **Column used in multiple selected expressions:** Data 360 SQL finds all grouping values before it checks each selected expression. Selecting a column by itself can group that column when it's also used inside another selected expression. - **`SELECT *` used:** The query groups on every column returned by `*`. ## Examples of `GROUP BY ALL` Each example uses a common table expression and `VALUES` list to provide the input rows. ### Group on Selected Columns This query groups by `a`, the only selected value that isn't calculated by an aggregate function. ```sql WITH t(a, b, c) AS ( VALUES (1, 10, 100), (1, 20, 200), (2, 10, 300), (2, 10, 400) ) SELECT a, sum(c) FROM t GROUP BY ALL; ``` This query is equivalent to the longer form. ```sql WITH t(a, b, c) AS ( VALUES (1, 10, 100), (1, 20, 200), (2, 10, 300), (2, 10, 400) ) SELECT a, sum(c) FROM t GROUP BY a; ``` Output: ```sql a | sum ---+----- 1 | 300 2 | 700 ``` ### Return Distinct Rows This query has no aggregate functions, so it groups on both selected columns. The result contains the same rows as `SELECT DISTINCT a, b`. ```sql WITH t(a, b, c) AS ( VALUES (1, 10, 100), (1, 20, 200), (2, 10, 300), (2, 10, 400) ) SELECT a, b FROM t GROUP BY ALL; ``` Output: ```sql a | b ---+---- 1 | 10 1 | 20 2 | 10 ``` ### Group on an Expression This query groups on the result of `a * b`, not on `a` and `b` separately. The rows `(1, 20)` and `(2, 10)` both produce `20`, so they belong to the same group. ```sql WITH t(a, b, c) AS ( VALUES (1, 10, 100), (1, 20, 200), (2, 10, 300), (2, 10, 400) ) SELECT a * b AS p, sum(c) FROM t GROUP BY ALL; ``` Output: ```sql p | sum ----+----- 10 | 100 20 | 900 ``` ### Aggregate All Rows This query selects only aggregate results. Because there are no other selected values to group on, all rows form one group, and the query returns one row. ```sql WITH t(a, b, c) AS ( VALUES (1, 10, 100), (1, 20, 200), (2, 10, 300), (2, 10, 400) ) SELECT sum(c), count(*) FROM t GROUP BY ALL; ``` Output: ```sql sum | count ------+------- 1000 | 4 ``` ### Use a Column Inside Another Expression This query causes an error. Data 360 SQL doesn't group on `a + sum(c)` because the expression contains the aggregate function `sum()`. As a result, column `a` isn't grouped. ```sql WITH t(a, b, c) AS ( VALUES (1, 10, 100), (1, 20, 200), (2, 10, 300), (2, 10, 400) ) SELECT a + sum(c) FROM t GROUP BY ALL; ``` Error: ```text column "a" must appear in the GROUP BY clause or be used in an aggregate function ``` This query works because selecting `a` by itself adds `a` to the group. The query can then use `a` in the expression `a + sum(c)`. ```sql WITH t(a, b, c) AS ( VALUES (1, 10, 100), (1, 20, 200), (2, 10, 300), (2, 10, 400) ) SELECT a, a + sum(c) FROM t GROUP BY ALL; ``` Output: ```sql a | ?column? ---+---------- 1 | 301 2 | 702 ``` ## `GROUPING SETS`, `CUBE`, and `ROLLUP` Use grouping sets to calculate aggregates for several combinations of columns in one query. Data 360 SQL creates the groups after it processes the `FROM` and `WHERE` clauses, then calculates the aggregates for each group. ### Define Grouping Sets Each item in `GROUPING SETS` defines one set of columns or expressions to group by. - An empty grouping set `()` combines all rows into one group. - A column or expression that isn't part of a grouping set has a null value in rows produced for that set. - To identify the grouping set that produced a row, use the [GROUPING function](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/aggregate-func/aggregate.md#grouping-function). By default, `GROUP BY` retains duplicate grouping sets. You can add `ALL` before an explicit grouping list to specify this behavior or add `DISTINCT` to remove duplicate grouping sets. ### Syntactic Sugar for Common Grouping Sets Use `ROLLUP` or `CUBE` instead of listing common grouping-set combinations individually. | Construct | Grouping Sets Generated | Common Use | | --------- | ------------------------------------------------------------- | ---------------------------------------- | | `ROLLUP` | The full list, each left-to-right prefix, and the empty list. | Analyze hierarchical data and subtotals. | | `CUBE` | Every possible subset of the list, including the empty list. | Analyze every combination of dimensions. | An element in `ROLLUP` or `CUBE` can be an expression or a parenthesized sublist. Data 360 SQL treats a parenthesized sublist as one unit when it generates grouping sets. You can use `ROLLUP` and `CUBE` directly in `GROUP BY` or inside `GROUPING SETS`. When a `GROUP BY` clause contains multiple grouping items, Data 360 SQL generates the cross product of those items. ## Examples of Grouping Sets ### `ROLLUP` Expansion This example shows a simple `ROLLUP` and its equivalent `GROUPING SETS`. ```sql ROLLUP (e1, e2, e3, ...) ``` ```sql GROUPING SETS ( (e1, e2, e3, ...), ... (e1, e2), (e1), () ) ``` ### `CUBE` Expansion This example shows a simple `CUBE` and its equivalent `GROUPING SETS`. ```sql CUBE (a, b, c) ``` ```sql GROUPING SETS ( (a, b, c), (a, b), (a, c), (a), (b, c), (b), (c), () ) ``` ### Keep Expressions Together These examples show how parenthesized sublists stay together when Data 360 SQL expands `CUBE` and `ROLLUP`. #### `CUBE` ```sql CUBE ((a, b), (c, d)) ``` ```sql GROUPING SETS ( (a, b, c, d), (a, b), (c, d), () ) ``` #### `ROLLUP` ```sql ROLLUP (a, (b, c), d) ``` ```sql GROUPING SETS ( (a, b, c, d), (a, b, c), (a), () ) ``` ### Combine Multiple Grouping Items This example shows the equivalent `GROUPING SETS` for `CUBE` if there are multiple grouping items. ```sql GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e)) ``` ```sql GROUP BY GROUPING SETS ( (a, b, c, d), (a, b, c, e), (a, b, d), (a, b, e), (a, c, d), (a, c, e), (a, d), (a, e) ) ``` ### Keep or Remove Duplicate Grouping Sets If you provide multiple grouping items together, the final set of grouping sets can contain duplicates. This example shows a `ROLLUP` with duplicate values in the equivalent `GROUPING SETS`. ```sql GROUP BY ROLLUP (a, b), ROLLUP (a, c) ``` ```sql GROUP BY GROUPING SETS ( (a, b, c), (a, b), (a, b), (a, c), (a), (a), (a, c), (a), () ) ``` To remove duplicates, use the `DISTINCT` clause on the `GROUP BY`. This example shows a `DISTINCT` `ROLLUP` and its equivalent `GROUPING SETS`. ```sql GROUP BY DISTINCT ROLLUP (a, b), ROLLUP (a, c) ``` ```sql GROUP BY GROUPING SETS ( (a, b, c), (a, b), (a, c), (a), () ) ``` ### Group Sample Sales Data This example shows the use of `GROUPING SETS` for the table `items_sold` that describes the brands, sizes, and number of items sold. `items_sold`: ```sql brand | size | sales ------+------+------ Foo | L | 10 Foo | M | 20 Bar | M | 15 Bar | L | 5 ``` ```sql SELECT * FROM items_sold; SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ()); ``` Output: ```sql brand | size | sum ------+------+----- Foo | | 30 | Bar | | 20 | | L | 15 | | M | 35 | | | 50 | ```