13.3 The GROUPING_ID and GROUP_ID Functions

Earlier in this chapter, you saw how to use the GROUPING function to distinguish between the regular GROUP BY rows and the summary rows produced by the GROUP BY extensions. Oracle9i extended the concept of the GROUPING function and introduced two more functions that you can use with a GROUP BY clause:

  • GROUPING_ID

  • GROUP_ID

These functions can be used only with a GROUP BY clause. However, unlike the GROUPING function that can only be used with a GROUP BY extension, the GROUPING_ID and GROUP_ID functions can be used in a query, even without a GROUP BY extension.

Although it is legal to use these two functions without a GROUP BY extension, using GROUPING_ID and GROUP_ID without ROLLUP, CUBE, or GROUPING SETS doesn't produce any meaningful output, because GROUPING_ID and GROUP_ID are 0 for all regular GROUP BY rows.


The following sections discuss these two functions in detail.

13.3.1 GROUPING_ID

The syntax of the GROUPING_ID function is as follows:

SELECT  . . .  , GROUPING_ID(ordered_list_of_grouping_columns)

FROM  . . . 

GROUP BY  . . .

The GROUPING_ID function takes an ordered list of grouping columns as input, and computes the output by working through the following steps:

  1. It generates the results of the GROUPING function as applied to each of the individual columns in the list. The result of this step is a set of ones and zeros.

  2. It puts these ones and zeros in the same order as the order of the columns in its argument list to produce a bit vector.

  3. Treating this bit vector (a series of ones and zeros) as a binary number, it converts the bit vector into a decimal (base 10) number.

  4. The decimal number computed in Step 3 is returned as the GROUPING_ID function's output.

The following example illustrates this process and compares the results from GROUPING_ID with those from GROUPING:

SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,

r.name region, SUM(o.tot_sales) total,

GROUPING(o.year) y, GROUPING(o.month) m, GROUPING(r.name) r,

GROUPING_ID (o.year, o.month, r.name) gid

FROM all_orders o JOIN region r

ON r.region_id = o.region_id

WHERE o.month BETWEEN 1 AND 3

GROUP BY CUBE (o.year, o.month, r.name);



YEAR MONTH     REGION              TOTAL   Y    M   R    GID

---- --------- -------------- ---------- --- ---- --- ------

2000 January   Mid-Atlantic      1221394   0    0   0      0

2000 January   New England       1018430   0    0   0      0

2000 January   Southeast US       758042   0    0   0      0

2000 January                     2997866   0    0   1      1

2000 February  Mid-Atlantic       857352   0    0   0      0

2000 February  New England       1231492   0    0   0      0

2000 February  Southeast US      1236846   0    0   0      0

2000 February                    3325690   0    0   1      1

2000 March     Mid-Atlantic      1274062   0    0   0      0

2000 March     New England       1132966   0    0   0      0

2000 March     Southeast US      1311986   0    0   0      0

2000 March                       3719014   0    0   1      1

2000           Mid-Atlantic      3352808   0    1   0      2

2000           New England       3382888   0    1   0      2

2000           Southeast US      3306874   0    1   0      2

2000                            10042570   0    1   1      3

2001 January   Mid-Atlantic       610697   0    0   0      0

2001 January   New England        509215   0    0   0      0

2001 January   Southeast US       379021   0    0   0      0

2001 January                     1498933   0    0   1      1

2001 February  Mid-Atlantic       428676   0    0   0      0

2001 February  New England        615746   0    0   0      0

2001 February  Southeast US       618423   0    0   0      0

2001 February                    1662845   0    0   1      1

2001 March     Mid-Atlantic       637031   0    0   0      0

2001 March     New England        566483   0    0   0      0

2001 March     Southeast US       655993   0    0   0      0

2001 March                       1859507   0    0   1      1

2001           Mid-Atlantic      1676404   0    1   0      2

2001           New England       1691444   0    1   0      2

2001           Southeast US      1653437   0    1   0      2

2001                             5021285   0    1   1      3

     January   Mid-Atlantic      1832091   1    0   0      4

     January   New England       1527645   1    0   0      4

     January   Southeast US      1137063   1    0   0      4

     January                     4496799   1    0   1      5

     February  Mid-Atlantic      1286028   1    0   0      4

     February  New England       1847238   1    0   0      4

     February  Southeast US      1855269   1    0   0      4

     February                    4988535   1    0   1      5

     March     Mid-Atlantic      1911093   1    0   0      4

     March     New England       1699449   1    0   0      4

     March     Southeast US      1967979   1    0   0      4

     March                       5578521   1    0   1      5

               Mid-Atlantic      5029212   1    1   0      6

               New England       5074332   1    1   0      6

               Southeast US      4960311   1    1   0      6

                                15063855   1    1   1      7



48 rows selected.

Note that the GROUPING_ID is the decimal equivalent of the bit vector generated by the individual GROUPING functions. In this output, the GROUPING_ID has values 0, 1, 2, 3, 4, 5, 6, and 7. Table 13-2 describes these aggregation levels.

Table 13-2. Result of GROUPING_ID(o.year, o.month, r.name)

Aggregation level

Bit vector

GROUPING_ID

Regular GROUP BY rows

0 0 0

0

Subtotal for Year-Month, aggregated at (Region)

0 0 1

1

Subtotal for Year-Region, aggregated at (Month)

0 1 0

2

Subtotal for Year, aggregated at (Month, Region)

0 1 1

3

Subtotal for Month-Region, aggregated at (Year)

1 0 0

4

Subtotal for Month, aggregated at (Year, Region)

1 0 1

5

Subtotal for Region, aggregated at (Year, Month)

1 1 0

6

Grand total for all levels, aggregated at (Year, Month, Region)

1 1 1

7


The GROUPING_ID function can be used effectively in a query to filter rows according to your requirement. Let's say you want only the summary rows to be displayed in the output, and not the regular GROUP BY rows. You can use the GROUPING_ID function in the HAVING clause to do this by restricting output to only those rows that contain totals and subtotals (i.e., for which GROUPING_ID > 0):

SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,

r.name region, SUM(o.tot_sales) total

FROM all_orders o JOIN region r

ON r.region_id = o.region_id

WHERE o.month BETWEEN 1 AND 3

GROUP BY CUBE (o.year, o.month, r.name)

HAVING GROUPING_ID (o.year, o.month, r.name) > 0;



     YEAR MONTH     REGION                    TOTAL

--------- --------- -------------------- ----------

                                           15063855

                    New England             5074332

                    Mid-Atlantic            5029212

                    Southeast US            4960311

          January                           4496799

          January   New England             1527645

          January   Mid-Atlantic            1832091

          January   Southeast US            1137063

          February                          4988535

          February  New England             1847238

          February  Mid-Atlantic            1286028

          February  Southeast US            1855269

          March                             5578521

          March     New England             1699449

          March     Mid-Atlantic            1911093

          March     Southeast US            1967979

     2000                                  10042570

     2000           New England             3382888

     2000           Mid-Atlantic            3352808

     2000           Southeast US            3306874

     2000 January                           2997866

     2000 February                          3325690

     2000 March                             3719014

     2001                                   5021285

     2001           New England             1691444

     2001           Mid-Atlantic            1676404

     2001           Southeast US            1653437

     2001 January                           1498933

     2001 February                          1662845

     2001 March                             1859507



30 rows selected.

As you can see, GROUPING_ID makes it easier to filter the output of aggregation operations. Without the GROUPING_ID function, you have to write a more complex query using the GROUPING function to achieve the same result. For example, the following query uses GROUPING rather than GROUPING_ID to display only totals and subtotals. Note the added complexity in the HAVING clause.

SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,

r.name region, SUM(o.tot_sales) total

FROM all_orders o JOIN region r

ON r.region_id = o.region_id

WHERE o.month BETWEEN 1 AND 3

GROUP BY CUBE (o.year, o.month, r.name)

HAVING GROUPING(o.year) > 0

OR GROUPING(o.month) > 0

OR GROUPING(r.name) > 0;



   YEAR MONTH     REGION                    TOTAL

------- --------- -------------------- ----------

                                         15063855

                  New England             5074332

                  Mid-Atlantic            5029212

                  Southeast US            4960311

        January                           4496799

        January   New England             1527645

        January   Mid-Atlantic            1832091

        January   Southeast US            1137063

        February                          4988535

        February  New England             1847238

        February  Mid-Atlantic            1286028

        February  Southeast US            1855269

        March                             5578521

        March     New England             1699449

        March     Mid-Atlantic            1911093

        March     Southeast US            1967979

   2000                                  10042570

   2000           New England             3382888

   2000           Mid-Atlantic            3352808

   2000           Southeast US            3306874

   2000 January                           2997866

   2000 February                          3325690

   2000 March                             3719014

   2001                                   5021285

   2001           New England             1691444

   2001           Mid-Atlantic            1676404

   2001           Southeast US            1653437

   2001 January                           1498933

   2001 February                          1662845

   2001 March                             1859507



30 rows selected.

13.3.2 GROUPING and GROUPING_ID in ORDER BY

The GROUPING and GROUPING_ID functions not only help you filter rows returned from queries using CUBE and ROLLUP, they can also help you to order those rows in a meaningful way. The order of the rows in a query's output is not guaranteed unless you use an ORDER BY clause in the query. However, if you order the results of a CUBE or ROLLUP query by one dimension, the order of the results may not be meaningful with respect to other dimensions. In such an aggregate query, you may prefer to order the results based on the number of dimensions involved rather than by individual dimensions. For example, when executing the previous section's query, you may prefer to see the output rows in the following order:

  1. Those rows representing an aggregate in one dimension

  2. Those rows representing an aggregate in two dimensions

  3. Those rows representing an aggregate in three dimensions

To achieve this ordering of rows, you need to use an ORDER BY clause that uses a combination of GROUPING and GROUPING_ID functions, as shown in the following example:

SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,

r.name region, SUM(o.tot_sales) total,

GROUPING_ID (o.year, o.month, r.name) gid,

GROUPING(o.year) + GROUPING(o.month) + GROUPING(r.name) sum_grouping

FROM all_orders o JOIN region r

ON r.region_id = o.region_id

WHERE o.month BETWEEN 1 AND 3

GROUP BY CUBE (o.year, o.month, r.name)

HAVING GROUPING(o.year) > 0

OR GROUPING(o.month) > 0

OR GROUPING(r.name) > 0

ORDER BY (GROUPING(o.year) + GROUPING(o.month) + GROUPING(r.name)),

GROUPING_ID (o.year, o.month, r.name);



  YEAR MONTH     REGION              TOTAL   GID SUM_GROUPING

------ --------- -------------- ---------- ----- ------------

  2000 January                     2997866     1            1

  2000 February                    3325690     1            1

  2000 March                       3719014     1            1

  2001 March                       1859507     1            1

  2001 February                    1662845     1            1

  2001 January                     1498933     1            1

  2000           New England       3382888     2            1

  2001           Mid-Atlantic      1676404     2            1

  2001           Southeast US      1653437     2            1

  2001           New England       1691444     2            1

  2000           Mid-Atlantic      3352808     2            1

  2000           Southeast US      3306874     2            1

       January   New England       1527645     4            1

       January   Mid-Atlantic      1832091     4            1

       January   Southeast US      1137063     4            1

       February  Southeast US      1855269     4            1

       March     Mid-Atlantic      1911093     4            1

       March     New England       1699449     4            1

       February  Mid-Atlantic      1286028     4            1

       February  New England       1847238     4            1

       March     Southeast US      1967979     4            1

  2000                            10042570     3            2

  2001                             5021285     3            2

       January                     4496799     5            2

       March                       5578521     5            2

       February                    4988535     5            2

                 New England       5074332     6            2

                 Mid-Atlantic      5029212     6            2

                 Southeast US      4960311     6            2

                                  15063855     7            3

In this output, the aggegate rows for individual dimensions, region, month, and year are shown first. These are followed by the aggregate rows for two dimensions: month and region, year and region, and year and month, respectively. The last row is the one aggregated over all three dimensions.

13.3.3 GROUP_ID

As you saw in previous sections, Oracle9i Database allows you to have repeating grouping columns and multiple grouping operations in a GROUP BY clause. Some combinations could result in duplicate rows in the output. The GROUP_ID distinguishes between otherwise duplicate result rows.

The syntax of the GROUP_ID function is:

SELECT  . . .  , GROUP_ID( )

FROM  . . . 

GROUP BY  . . .

The GROUP_ID function takes no argument, and returns 0 through n - 1, where n is the occurrence count for duplicates. The first occurrence of a given row in the output of a query will have a GROUP_ID of 0, the second occurrence of a given row will have a GROUP_ID of 1, and so forth. The following example illustrates the use of the GROUP_ID function:

SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,

r.name region, SUM(o.tot_sales) total, GROUP_ID( )

FROM all_orders o JOIN region r

ON r.region_id = o.region_id

WHERE o.month BETWEEN 1 AND 3

GROUP BY o.year, ROLLUP (o.year, o.month, r.name);



      YEAR MONTH     REGION                    TOTAL GROUP_ID( )

---------- --------- -------------------- ---------- ----------

      2000 January   Mid-Atlantic            1221394          0

      2000 January   New England             1018430          0

      2000 January   Southeast US             758042          0

      2000 January                           2997866          0

      2000 February  Mid-Atlantic             857352          0

      2000 February  New England             1231492          0

      2000 February  Southeast US            1236846          0

      2000 February                          3325690          0

      2000 March     Mid-Atlantic            1274062          0

      2000 March     New England             1132966          0

      2000 March     Southeast US            1311986          0

      2000 March                             3719014          0

      2001 January   Mid-Atlantic             610697          0

      2001 January   New England              509215          0

      2001 January   Southeast US             379021          0

      2001 January                           1498933          0

      2001 February  Mid-Atlantic             428676          0

      2001 February  New England              615746          0

      2001 February  Southeast US             618423          0

      2001 February                          1662845          0

      2001 March     Mid-Atlantic             637031          0

      2001 March     New England              566483          0

      2001 March     Southeast US             655993          0

      2001 March                             1859507          0

      2000                                  10042570          0

      2001                                   5021285          0

      2000                                  10042570          1

      2001                                   5021285          1



28 rows selected.

Note that the value 1 is returned by the GROUP_ID function for the last two rows. These rows are indeed duplicates of the previous two rows. If you don't want to see the duplicates in your result set, restrict your query's results to GROUP_ID 0:

SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,

r.name region, SUM(o.tot_sales) total

FROM all_orders o JOIN region r

ON r.region_id = o.region_id

WHERE o.month BETWEEN 1 AND 3

GROUP BY o.year, ROLLUP (o.year, o.month, r.name)

HAVING GROUP_ID( ) = 0;



      YEAR MONTH     REGION                    TOTAL

---------- --------- -------------------- ----------

      2000 January   New England             1018430

      2000 January   Mid-Atlantic            1221394

      2000 January   Southeast US             758042

      2000 January                           2997866

      2000 February  New England             1231492

      2000 February  Mid-Atlantic             857352

      2000 February  Southeast US            1236846

      2000 February                          3325690

      2000 March     New England             1132966

      2000 March     Mid-Atlantic            1274062

      2000 March     Southeast US            1311986

      2000 March                             3719014

      2001 January   New England              509215

      2001 January   Mid-Atlantic             610697

      2001 January   Southeast US             379021

      2001 January                           1498933

      2001 February  New England              615746

      2001 February  Mid-Atlantic             428676

      2001 February  Southeast US             618423

      2001 February                          1662845

      2001 March     New England              566483

      2001 March     Mid-Atlantic             637031

      2001 March     Southeast US             655993

      2001 March                             1859507

      2000                                  10042570

      2001                                   5021285



26 rows selected.

This version of the query uses HAVING GROUP_ID( ) = 0 to eliminate the two duplicate totals from the result set. GROUP_ID is only meaningful in the HAVING clause, because it applies to summarized data. You can't use GROUP_ID in a WHERE clause, and it wouldn't make sense to try.