grouping Function

70. Which two statements are true about the GROUPING function? (Choose two.)

A. It is used to find the groups forming the subtotal in a row.
B. It is used to identify the NULL value in the aggregate functions.
C. It is used to form the group sets involved in generating the totals and
subtotals.
D. It can only be used with ROLLUP and CUBE operators specified in the GROUP
BY clause.
Answer: AD

GROUPING Functions

Two challenges arise with the use of ROLLUP and CUBE. First, how can you programmatically determine which result set rows are subtotals, and how do you find the exact level of aggregation for a given subtotal? You often need to use subtotals in calculations such as percent-of-totals, so you need an easy way to determine which rows are the subtotals. Second, what happens if query results contain both stored NULL values and "NULL" values created by a ROLLUP or CUBE? How can you differentiate between the two? See Oracle Database SQL Reference for syntax and restrictions.

GROUPING Function

GROUPING handles these problems. Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBEoperation. That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0.

GROUPING appears in the selection list portion of a SELECT statement. Its form is:

SELECT …  [GROUPING(dimension_column)…]  … 
  GROUP BY …    {CUBE | ROLLUP| GROUPING SETS}  (dimension_column)

Example 20-6 GROUPING to Mask Columns

This example uses GROUPING to create a set of mask columns for the result set shown in Example 20-3. The mask columns are easy to analyze programmatically.

SELECT channel_desc, calendar_month_desc, country_iso_code, 
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$, GROUPING(channel_desc) AS Ch,
   GROUPING(calendar_month_desc) AS Mo, GROUPING(country_iso_code) AS Co
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id 
  AND sales.cust_id=customers.cust_id
  AND customers.country_id = countries.country_id 
  AND sales.channel_id= channels.channel_id 
  AND channels.channel_desc IN ('Direct Sales', 'Internet') 
  AND times.calendar_month_desc IN ('2000-09', '2000-10') 
  AND countries.country_iso_code IN ('GB', 'US')
GROUP BY ROLLUP(channel_desc, calendar_month_desc, countries.country_iso_code);

CHANNEL_DESC         CALENDAR CO SALES$                 CH         MO         CO
-------------------- -------- -- -------------- ---------- ---------- ----------
Internet             2000-09  GB         16,569          0          0          0
Internet             2000-09  US        124,224          0          0          0
Internet             2000-09            140,793          0          0          1
Internet             2000-10  GB         14,539          0          0          0
Internet             2000-10  US        137,054          0          0          0
Internet             2000-10            151,593          0          0          1
Internet                                292,387          0          1          1
Direct Sales         2000-09  GB         85,223          0          0          0
Direct Sales         2000-09  US        638,201          0          0          0
Direct Sales         2000-09            723,424          0          0          1
Direct Sales         2000-10  GB         91,925          0          0          0
Direct Sales         2000-10  US        682,297          0          0          0
Direct Sales         2000-10            774,222          0          0          1
Direct Sales                          1,497,646          0          1          1
                                      1,790,032          1          1          1

A program can easily identify the detail rows by a mask of "0 0 0" on the T, R, and D columns. The first level subtotal rows have a mask of "0 0 1", the second level subtotal rows have a mask of "0 1 1", and the overall total row has a mask of "1 1 1".

You can improve the readability of result sets by using the GROUPING and DECODE functions as shown in Example 20-7.

Example 20-7 GROUPING For Readability

SELECT DECODE(GROUPING(channel_desc), 1, 'Multi-channel sum', channel_desc) AS
 Channel, DECODE (GROUPING (country_iso_code), 1, 'Multi-country sum',
 country_iso_code) AS Country, TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id 
  AND sales.cust_id=customers.cust_id 
  AND customers.country_id = countries.country_id 
  AND sales.channel_id= channels.channel_id 
  AND channels.channel_desc IN ('Direct Sales', 'Internet') 
  AND times.calendar_month_desc= '2000-09'
  AND country_iso_code IN ('GB', 'US')
GROUP BY CUBE(channel_desc, country_iso_code);

CHANNEL              COUNTRY           SALES$
-------------------- ----------------- --------------
Multi-channel sum    Multi-country sum        864,217
Multi-channel sum    GB                       101,792
Multi-channel sum    US                       762,425
Internet             Multi-country sum        140,793
Internet             GB                        16,569
Internet             US                       124,224
Direct Sales         Multi-country sum        723,424
Direct Sales         GB                        85,223
Direct Sales         US                       638,201

To understand the previous statement, note its first column specification, which handles the channel_desc column. Consider the first line of the previous statement:

SELECT DECODE(GROUPING(channel_desc), 1, 'All Channels', channel_desc)AS Channel

In this, the channel_desc value is determined with a DECODE function that contains a GROUPING function. The GROUPING function returns a 1 if a row value is an aggregate created by ROLLUP or CUBE, otherwise it returns a 0. The DECODE function then operates on the GROUPING function's results. It returns the text "All Channels" if it receives a 1 and the channel_desc value from the database if it receives a 0. Values from the database will be either a real value such as "Internet" or a stored NULL. The second column specification, displaying country_id, works the same way.

When to Use GROUPING

The GROUPING function is not only useful for identifying NULLs, it also enables sorting subtotal rows and filtering results. In Example 20-8, you retrieve a subset of the subtotals created by a CUBE and none of the base-level aggregations. The HAVING clause constrains columns that use GROUPING functions.

Example 20-8 GROUPING Combined with HAVING

SELECT channel_desc, calendar_month_desc, country_iso_code, TO_CHAR(
SUM(amount_sold), '9,999,999,999') SALES$, GROUPING(channel_desc) CH, GROUPING
  (calendar_month_desc)  MO, GROUPING(country_iso_code) CO
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id 
  AND customers.country_id = countries.country_id 
  AND sales.channel_id= channels.channel_id 
  AND channels.channel_desc IN ('Direct Sales', 'Internet') 
  AND times.calendar_month_desc IN ('2000-09', '2000-10') 
  AND country_iso_code IN ('GB', 'US')
GROUP BY CUBE(channel_desc, calendar_month_desc, country_iso_code)
HAVING (GROUPING(channel_desc)=1 AND GROUPING(calendar_month_desc)= 1 
  AND GROUPING(country_iso_code)=1) OR (GROUPING(channel_desc)=1 
  AND GROUPING (calendar_month_desc)= 1) OR (GROUPING(country_iso_code)=1
  AND GROUPING(calendar_month_desc)= 1);

CHANNEL_DESC         C CO SALES$                 CH         MO         CO
-------------------- - -- -------------- ---------- ---------- ----------
                       US      1,581,775          1          1          0
                       GB        208,257          1          1          0
Direct Sales                   1,497,646          0          1          1
Internet                         292,387          0          1          1
                               1,790,032          1          1          1

Compare the result set of Example 20-8 with that in Example 20-3 to see how Example 20-8 is a precisely specified group: it contains only the yearly totals, regional totals aggregated over time and department, and the grand total.