GROUP BY expression [,expression]*GROUP BY clause
The GROUP BY clause denotes that rows should be grouped according to the specified expression values. One row is returned for each group, after optionally filtering those aggregate rows based on a HAVING clause.
The general form of the GROUP BY is:
GROUP BY ROLLUP(expression [,expression]*)Syntax Rules
-
Column references in the group by cannot be made to alias names in the SELECT clause.
-
Expressions used in the group by must appear in the select clause.
-
Column references and expressions in the SELECT/HAVING/ORDER BY clauses that are not used in the group by clause must appear in aggregate functions.
-
If an aggregate function is used in the SELECT clause and no GROUP BY is specified, an implicit GROUP BY will be performed with the entire result set as a single group. In this case, every column in the SELECT must be an aggregate function as no other column value will be fixed across the entire group.
-
The GROUP BY columns must be of a comparable type.
Rollups
Just like normal grouping, ROLLUP processing logically occurs before the HAVING clause is processed. A ROLLUP of expressions will produce the same output as a regular grouping with the addition of aggregate values computed at higher aggregation levels. For N expressions in the ROLLUP, aggregates will be provided over (), (expr1), (expr1, expr2), etc. up to (expr1, … exprN-1), with the other grouping expressions in the output as null values. The following example uses a normal aggregation query:
SELECT country, city, sum(amount) from sales group by country, cityThe query returns the following data:
| country | city | sum(amount) |
|---|---|---|
US |
St. Louis |
10000 |
US |
Raleigh |
150000 |
US |
Denver |
20000 |
UK |
Birmingham |
50000 |
UK |
London |
75000 |
In contrast, the following example uses a rollup query:
Data returned from a rollup query
SELECT country, city, sum(amount) from sales group by rollup(country, city)would return:
| country | city | sum(amount) |
|---|---|---|
US |
St. Louis |
10000 |
US |
Raleigh |
150000 |
US |
Denver |
20000 |
US |
<null> |
180000 |
UK |
Birmingham |
50000 |
UK |
London |
75000 |
UK |
<null> |
125000 |
<null> |
<null> |
305000 |
|
Note
|
Not all sources are compatible with ROLLUPs, and compared to normal aggregate processing, some optimizations might be inhibited by the use of a ROLLUP. |
The use of ROLLUPs in Teiid is currently limited in comparison to the SQL specification.