Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Syntax
Purpose
GROUPING_ID
returns a number corresponding to the GROUPING
bit vector associated with a row. GROUPING_ID
is applicable only in a SELECT
statement that contains a GROUP
BY
extension, such as ROLLUP
or CUBE
, and a GROUPING
function. In queries with many GROUP
BY
expressions, determining the GROUP
BY
level of a particular row requires many GROUPING
functions, which leads to cumbersome SQL. GROUPING_ID
is useful in these cases.
GROUPING_ID
is functionally equivalent to taking the results of multiple GROUPING
functions and concatenating them into a bit vector (a string of ones and zeros). By using GROUPING_ID
you can avoid the need for multiple GROUPING
functions and make row filtering conditions easier to express. Row filtering is easier with GROUPING_ID
because the desired rows can be identified with a single condition of GROUPING_ID
= n
. The function is especially useful when storing multiple levels of aggregation in a single table.
Examples
The following example shows how to extract grouping IDs from a query of the sample table sh.sales
:
SELECT channel_id, promo_id, sum(amount_sold) s_sales, GROUPING(channel_id) gc, GROUPING(promo_id) gp, GROUPING_ID(channel_id, promo_id) gcp, GROUPING_ID(promo_id, channel_id) gpc FROM sales WHERE promo_id > 496 GROUP BY CUBE(channel_id, promo_id); C PROMO_ID S_SALES GC GP GCP GPC - ---------- ---------- ---------- ---------- ---------- ---------- C 497 26094.35 0 0 0 0 C 498 22272.4 0 0 0 0 C 499 19616.8 0 0 0 0 C 9999 87781668 0 0 0 0 C 87849651.6 0 1 1 2 I 497 50325.8 0 0 0 0 I 498 52215.4 0 0 0 0 I 499 58445.85 0 0 0 0 I 9999 169497409 0 0 0 0 I 169658396 0 1 1 2 P 497 31141.75 0 0 0 0 P 498 46942.8 0 0 0 0 P 499 24156 0 0 0 0 P 9999 70890248 0 0 0 0 P 70992488.6 0 1 1 2 S 497 110629.75 0 0 0 0 S 498 82937.25 0 0 0 0 S 499 80999.15 0 0 0 0 S 9999 267205791 0 0 0 0 S 267480357 0 1 1 2 T 497 8319.6 0 0 0 0 T 498 5347.65 0 0 0 0 T 499 19781 0 0 0 0 T 9999 28095689 0 0 0 0 T 28129137.3 0 1 1 2 497 226511.25 1 0 2 1 498 209715.5 1 0 2 1 499 202998.8 1 0 2 1 9999 623470805 1 0 2 1 624110031 1 1 3 3