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.

Syntax

SELECT [ALL | DISTINCT [ON (<expression> [, ...])]]
    ...
    [GROUP BY { ALL | [ALL | DISTINCT] <grouping_element> [, ...] }]
    ...

grouping_element can be one of:

()
<expression>
(<expression>s [, ...])
ROLLUP ({ <expression> | (<expression> [, ...])} [, ...])
CUBE ({ <expression> | (<expression> [, ...])} [, ...])
GROUPING SETS (<grouping_element> [, ...])

Choose Values to Group By

For each <grouping_element>, 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.

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.

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:

 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.

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:

 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.

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:

 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.

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:

 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.

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:

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

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:

 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.

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.

ROLLUP (e1, e2, e3, ...)

GROUPING SETS (
    (e1, e2, e3, ...),
    ...
    (e1, e2),
    (e1),
    ()
)

`CUBE` Expansion

This example shows a simple CUBE and its equivalent GROUPING SETS.

CUBE (a, b, c)

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`

CUBE ((a, b), (c, d))

GROUPING SETS (
    (a, b, c, d),
    (a, b),
    (c, d),
    ()
)

`ROLLUP`

ROLLUP (a, (b, c), d)

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.

GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))

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.

GROUP BY ROLLUP (a, b), ROLLUP (a, c)

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.

GROUP BY DISTINCT ROLLUP (a, b), ROLLUP (a, c)

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:

brand | size | sales
------+------+------
Foo   | L    | 10
Foo   | M    | 20
Bar   | M    | 15
Bar   | L    | 5

SELECT * FROM items_sold;
SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());

Output:

brand | size | sum
------+------+-----
Foo   |      | 30 |
Bar   |      | 20 |
      |   L  | 15 |
      |   M  | 35 |
      |      | 50 |

GROUP BY Clause

Syntax

Choose Values to Group By

GROUP BY ALL

Examples of GROUP BY ALL

Group on Selected Columns

Return Distinct Rows

Group on an Expression

Aggregate All Rows

Use a Column Inside Another Expression

GROUPING SETS, CUBE, and ROLLUP

Define Grouping Sets

Syntactic Sugar for Common Grouping Sets

Examples of Grouping Sets

ROLLUP Expansion

CUBE Expansion

Keep Expressions Together

CUBE

ROLLUP

Combine Multiple Grouping Items

Keep or Remove Duplicate Grouping Sets

Group Sample Sales Data

`GROUP BY ALL`

Examples of `GROUP BY ALL`

`GROUPING SETS`, `CUBE`, and `ROLLUP`

`ROLLUP` Expansion

`CUBE` Expansion

`CUBE`

`ROLLUP`