Mastering Oracle SQL

Oracle9i Grouping Features

The grouping examples you have seen so far represent simple ways of aggregating data using the extensions of the GROUP BY clause. Oracle9i provides ways to aggregate data for more complex requirements. The next sections discuss these features in detail:

• Repeating column names in the GROUP BY clause

• Grouping on composite columns

• Concatenated groupings

• The GROUPING_ID and GROUP_ID functions

Repeating Column Names in the GROUP BY Clause

In Oracle8i, repeating column names are not allowed in a GROUP BY clause. If the GROUP BY clause contains an extension (i.e., ROLLUP or CUBE), you cannot use the same column inside the extension as well as outside the extension. The following SQL will be invalid in Oracle8i and throw an error:

               SELECT O.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,
               R.NAME REGION, SUM(O.TOT_SALES) Total
               FROM ORDERS O, REGION R
               WHERE R.REGION_ID = O.REGION_ID
               AND O.MONTH BETWEEN 1 AND 3
               GROUP BY O.YEAR, ROLLUP (O.YEAR, O.MONTH, R.NAME);
GROUP BY O.YEAR, ROLLUP (O.YEAR, O.MONTH, R.NAME)
                         *
ERROR at line 6:
ORA-30490: Ambiguous expression in GROUP BY ROLLUP or CUBE list

However, the same query works in Oracle9i:

               SELECT O.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,
               R.NAME REGION, SUM(O.TOT_SALES) TOTAL
               FROM ORDERS O, REGION R
               WHERE R.REGION_ID = O.REGION_ID
               AND O.MONTH BETWEEN 1 AND 3
               GROUP BY O.YEAR, ROLLUP (O.YEAR, O.MONTH, R.NAME); YEAR MONTH REGION TOTAL ---------- --------- -------------------- ---------- 2000 January Mid-Atlantic ...