THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
Aggregate Query with GROUPING SETS | Equivalent Aggregate Query with GROUP BY |
|---|---|
SELECT a, b, SUM(c) FROM tab1 GROUP BY GROUPING SETS ( (a,b) ) | SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b |
SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a,b), a) | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b
SELECT a, null, SUM( c ) FROM tab1 GROUP BY a |
SELECT a,b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS (a,b) | SELECT a, null, SUM( c ) FROM tab1 GROUP BY a
SELECT null, b, SUM( c ) FROM tab1 GROUP BY b |
SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a, b), a, b, ( ) ) | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b
SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null
SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b
SELECT null, null, SUM( c ) FROM tab1 |
Grouping__ID function
When aggregates are displayed for a column its value is null. This may conflict in case the column itself has some null values. There needs to be some way to identify NULL in column, which means aggregate and NULL in column, which means value. GROUPING__ID function is the solution to that.
This function returns a flag a bitvector corresponding to whether each column is present or not. For each column, a value of "1" is produced for a row in
the result set if that column has been aggregated in that row. Otherwise , otherwise the value is "0". There can be only one column expression as the argument of the GROUPING function and that column should also be in the SELECT. GROUPING function This can be used to substitute the NULL value, which usually appears in columns at the aggregation level by something meaningful like Total.
GROUPING function has the general syntax of GROUPING ( ). It is used only in SELECT clause. It takes only a single column expression as argument.
GROUPING_ID takes a set of columns. It applies the GROUPING function on each column in its argument and composes a bit vector with the "0" and "1" values. It returns the decimal equivalent of the bit vectordifferentiate when there are nulls
in the data.
Cubes and Rollups
The general syntax is CUBE ( ). It is used with the GROUP BY only. CUBE creates a subtotal of all possible combinations of the set of column in its argument. Once we compute a CUBE on a set of dimension, we can get answer to all possible aggregation questions on those dimensions.
...