The key advantages of CUBE and ROLLUP are that they will allow more robust aggregations than COMPUTE and they work with any SQL-enabled tool.
GROUPING SETS allows to specify more than one GROUP BY options in the same record set. Logically expressed as seperate GROUP BY queries connected by UNION.
select a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a,b), a) == select ... tab1 GROUP BY a, b UNION select a, null, SUM( c ) ...GROUP BY a
ROLLUP creates subtotals moving left to right using columns provided in ROLLUP. A grand total is provided only if the ROLLUP includes all the cols in GROUP BY clause.
The order of the columns in the ROLLUP modifier is significant, When you reverse the order, you get different subtotal.
GROUP BY ROLLUP (a, b, c) is equivalent to GROUPING SETS ( (a, b, c), (a, b), (a), ( )).
The CUBE modifier in the GROUP BY clause creates subtotals for all possible combinations of grouping columns. order of cols is not significant here unlike ROLLUP.
GROUP BY CUBE( a, b, c) is equivalent to GROUP BY GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )).
The GROUPING(expr) function returns a 1 for these summary rows (which has a null) and a 0 for the nonsummary rows for expr. Only for ROLLUP and CUBE.
You can use the GROUPING function in the HAVING clause to filter out rows.
GROUPING_ID (<col_list>) - Simplifies the above grouping function. It is derived from the GROUPING function by concatenating the GROUPING levels together as bits.
The GROUPING_ID function returns the exact level of the group.
GROUP_ID( ) takes no arguments and requires a GROUP BY clause. GROUP_ID returns a numeric identifier that can be used to uniquely identify duplicate groups.
Assigns a unique number for each duplicate group.For i duplicate groups, It will return values 0 through i-1. Can be used in HAVING to filter out duplicate rows.