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
.