| 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: 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: 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. 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. 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. 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: Those rows representing an aggregate in one dimension Those rows representing an aggregate in two dimensions 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. |
