Grouping Sets, ROLL UP & CUBE

Grouping Sets

This option allows us to create sets for each field specified in a GROUP BY of aggregation queries. Therefore, instead of combining field values, we will obtain subtotals for each field value.
The data return FROM and WHERE clauses are grouped separately by each specified grouping set is then aggregates according to the simple group BY clause, and then the results are returned.

SELECT col1, col2, aggregate_function(col)
FROM table-name
GROUP BY GROUPING SETS (col1,col2)

ROLL Up

A clause of the form ROLLUP ( e1, e2, e3, ... ) represents the given list of expressions and all prefixes of the list including the empty list.The rollup extension produces group subtotals from right to left and a grand total. If the number of columns listed in the rollup is M, the subtotals will have M + 1 levels.

SELECT col1,col2, aggregate_function(col)
FROM table_name
GROUP BY ROLLUP(col1,col2)

CUBE

A clause of the form CUBE ( e1, e2, ... ) represents the given list and all of its possible subsets (i.e. the power set).The Cube extension will generate subtotals for all the combinations of the columns specified in the Group by clause.If there are m number of the columns specified then 2^m will be subtotal combinations.

SELECT col1,col2, aggregate_function(col)
FROM table_name
GROUP BY CUBE(col1,col2)

Example :

\\Grouping Sets
=> SELECT * FROM CLASS;
class | Marks |  Language
-------+------+-------
A     | 90    |  Html
A     | 70    |  Css
B     | 80    |  Html
B     | 85    |  Css
=> SELECT class, Language, sum(Marks) FROM CLASS GROUP BY GROUPING SETS ((class), (Language), ());
class | Marks |  Language
-------+------+-------
A      | 160   |  
B      | 165   |  
| 170   |  Html
| 155   |  Css
\\ ROLL UP
ROLLUP ( x1, x2, x3, ... )
thus it is equivalent to
GROUPING SETS (
( x1, x2, x3, ... ),
...
( x1, x2 ),
( x1 ),
( )
)
\\CUBE
CUBE ( x, y, z )
thus, it is equivalent to
GROUPING SETS (
( x, y, z ),
( x, y   ),
( x,    z ),
( x       ),
(    y, z ),
(    y    ),
(       z ),
(         )
)

Visit :

DDL commands

Discussion