Mastering Oracle SQL

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 extends the concept of the GROUPING function and introduces two new functions that you can use with a GROUP BY clause:

• GROUPING_ID

• GROUP_ID

These functions can only be used 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.

Tip

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.

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. First, 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.