GROUP BY Clause

GROUP BY combines all selected rows that share values for the grouped expressions into one row. The <expression> in a grouping_element can be an input column name, the name, or ordinal number of an output column, or an expression formed from input-column values. If there’s ambiguity, Data Cloud SQL interprets a GROUP BY name as an input-column name.

If you use GROUPING SETS, ROLLUP, or CUBE, then the GROUP BY clause defines grouping sets. The optional DISTINCT clause removes duplicate sets before processing. For more information, see Grouping Sets.

The query computes aggregate functions across all rows in each group, producing a separate value for each group. When you use GROUP BY or aggregate functions, the SELECT list expressions can’t refer to ungrouped columns except in aggregate functions. The query evaluates aggregate functions before evaluating any scalar expressions in the HAVING clause or SELECT list. See Aggregate Functions.

The optional GROUP BY clause has the general form GROUP BY <grouping_element> [, ...].

grouping_element can be one of:

The FROM and WHERE clauses group data in separate grouping sets. The aggregates are then computed for each group and then the results are returned.

Each sublist of GROUPING SETS can specify zero or more columns or expressions. The sublist is interpreted as though it were in the GROUP BY clause. An empty grouping set means that all rows are aggregated down to a single group.

Columns and expressions that aren’t in the grouping set are replaced by null values. To determine which grouping an output row is from, use the GROUPING function.

A shorthand notation is provided for specifying two common types of grouping set.

  • ROLLUP represents the given list of expressions and all prefixes of the list including the empty list. ROLLUP is commonly used for analysis over hierarchical data.

  • CUBE represents the given list and all of its possible subsets.

The elements of a CUBE or ROLLUP clause can be expressions or sublists of elements in parentheses. Sublists in parentheses are treated as units for the purposes of generating the grouping sets.

You can use the CUBE and ROLLUP constructs in the GROUP BY cause or nested in GROUPING SETS clause. If multiple grouping items are specified in a GROUP BY clause, the final list of grouping sets is the cross product of the items.

These examples show a simple ROLLUP and its equivalent GROUPING SETS.

These examples show a simple CUBE and its equivalent GROUPING SETS.

These examples show a CUBE and ROLLUP clause with sublists of elements in parentheses.

CUBE

ROLLUP

This example shows the equivalent GROUPING SETS for CUBE if there are multiple grouping items.

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.

To remove duplicates, use the DISTINCT clause on the GROUP BY. This example shows a DISTINCT ROLLUP and its equivalent GROUPING SETS.