GROUPING SETS Aggregate
The GROUPING SETS
aggregate is an extension to the GROUP BY
clause that automatically performs subtotal aggregations on groupings that you specify.
You can use the GROUPING SETS
clause with three grouping functions:
To sort data, use the ORDER BY
clause. The ORDER BY
clause must follow the GROUP BY
clause.
Syntax
GROUP BY group-expression[,…]
Parameters
group-expression |
One or both of the following:
|
Defining the Groupings
GROUPING SETS allows you to specify exactly which groupings you want in the results. You can also concatenate the groupings as follows:
The following example clauses result in the groupings shown.
This clause... | Defines groupings... |
---|---|
...GROUP BY GROUPING SETS(A,B,C,D)... |
(A), (B), (C), (D) |
...GROUP BY GROUPING SETS((A),(B),(C),(D))... |
(A), (B), (C), (D) |
...GROUP BY GROUPING SETS((A,B,C,D))... |
(A, B, C, D) |
...GROUP BY GROUPING SETS(A,B),GROUPING SETS(C,D)... |
(A, C), (B, C), (A, D), (B, C) |
...GROUP BY GROUPING SETS((A,B)),GROUPING SETS(C,D)... |
(A, B, C), (A, B, D) |
...GROUP BY GROUPING SETS(A,B),GROUPING SETS(ROLLUP(C,D))... |
(A,B), (A,B,C), (A,B,C,D) |
...GROUP BY A,B,C,GROUPING SETS(ROLLUP(C, D))... |
(A, B, C, D), (A, B, C), (A, B, C) The clause contains two groups (A, B, C). In the HAVING clause, use the GROUP_ID function as a predicate, to eliminate the second grouping. |
Example: Selecting Groupings
This example shows how to select only those groupings you want. Suppose you want to aggregate on columns only, and you do not need the grand total. The first query omits the total. In the second query, you add () to the GROUPING SETS list to get the total. Use the ORDER BY clause to sort the results by grouping:
=> SELECT Category, Year, SUM(Amount) FROM expenses
GROUP BY GROUPING SETS((Category, Year), (Year)) ORDER BY 1, 2, GROUPING_ID(); Category | Year | SUM ------------+------+-------- Books | 2005 | 39.98 Books | 2007 | 29.99 Books | 2008 | 29.99 Electrical | 2005 | 109.99 Electrical | 2006 | 109.99 Electrical | 2007 | 229.98 | 2005 | 149.97 | 2006 | 109.99 | 2007 | 259.97 | 2008 | 29.99
=> SELECT Category, Year, SUM(Amount) FROM expenses
GROUP BY GROUPING SETS((Category, Year), (Year), ()) ORDER BY 1, 2, GROUPING_ID(); Category | Year | SUM ------------+------+-------- Books | 2005 | 39.98 Books | 2007 | 29.99 Books | 2008 | 29.99 Electrical | 2005 | 109.99 Electrical | 2006 | 109.99 Electrical | 2007 | 229.98 | 2005 | 149.97 | 2006 | 109.99 | 2007 | 259.97 | 2008 | 29.99 | | 549.92