|
|
|
12.5 Oracle9i Grouping FeaturesThe 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:
12.5.1 Repeating Column Names in the GROUP BY ClauseIn 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 1221394
2000 January New England 1018430
2000 January SouthEast US 758042
2000 January 2997866
2000 February Mid-Atlantic 857352
2000 February New England 1231492
2000 February SouthEast US 1236846
2000 February 3325690
2000 March Mid-Atlantic 1274062
2000 March New England 1132966
2000 March SouthEast US 1311986
2000 March 3719014
2001 January Mid-Atlantic 610697
2001 January New England 509215
2001 January SouthEast US 379021
2001 January 1498933
2001 February Mid-Atlantic 428676
2001 February New England 615746
2001 February SouthEast US 618423
2001 February 1662845
2001 March Mid-Atlantic 637031
2001 March New England 566483
2001 March SouthEast US 655993
2001 March 1859507
2000 10042570
2001 5021285
2000 10042570
2001 5021285
28 rows selected.
Repetition of O.YEAR in the GROUP BY clause as well as in the ROLLUP operation repeats the summary rows of each year in the output and suppresses the grand total. Repetition of column names in a GROUP BY clause isn't very useful, but it's worth knowing that such constructs are allowed in Oracle9i. 12.5.2 Grouping on Composite ColumnsOracle8i supports grouping on individual columns only. Oracle9i extends the grouping operations to include grouping on composite columns. A composite column is a collection of two or more columns, but their values are treated as one for the grouping computation. Oracle8i allows group operations of the form ROLLUP (a,b,c), whereas, Oracle9i allows group operations of the form ROLLUP (a,(b,c)) as well. In this case, (b,c) is treated as one column for the purpose of the grouping computation. For example: 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 ROLLUP ((O.YEAR, O.MONTH),R.NAME);
YEAR MONTH REGION TOTAL
---------- --------- -------------------- ----------
2000 January Mid-Atlantic 1221394
2000 January New England 1018430
2000 January SouthEast US 758042
2000 January 2997866
2000 February Mid-Atlantic 857352
2000 February New England 1231492
2000 February SouthEast US 1236846
2000 February 3325690
2000 March Mid-Atlantic 1274062
2000 March New England 1132966
2000 March SouthEast US 1311986
2000 March 3719014
2001 January Mid-Atlantic 610697
2001 January New England 509215
2001 January SouthEast US 379021
2001 January 1498933
2001 February Mid-Atlantic 428676
2001 February New England 615746
2001 February SouthEast US 618423
2001 February 1662845
2001 March Mid-Atlantic 637031
2001 March New England 566483
2001 March SouthEast US 655993
2001 March 1859507
15063855
25 rows selected.
In this example, two columns (O.YEAR, O.MONTH) are treated as one composite column. This causes Oracle to treat the combination of year and month as one dimension, and the summary rows are computed accordingly. Note that while this query is not allowed in Oracle8i, you can fake composite column groupings in Oracle8i by using the concatenation operator (||) to combine two columns and treat the result as one composite column. Oracle8i can then produce the same result as the previous query in Oracle 9i. For example: SELECT TO_CHAR(O.YEAR)||' '||TO_CHAR(TO_DATE(O.MONTH,'MM'),'Month')
Year_Month,
R.NAME REGION, SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
AND O.MONTH BETWEEN 1 AND 3
GROUP BY
ROLLUP (TO_CHAR(O.YEAR)||' '||TO_CHAR(TO_DATE(O.MONTH,'MM'),'Month'), R.NAME);
YEAR_MONTH REGION SUM(O.TOT_SALES)
-------------------- -------------------- ----------------
2000 February Mid-Atlantic 857352
2000 February New England 1231492
2000 February SouthEast US 1236846
2000 February 3325690
2000 January Mid-Atlantic 1221394
2000 January New England 1018430
2000 January SouthEast US 758042
2000 January 2997866
2000 March Mid-Atlantic 1274062
2000 March New England 1132966
2000 March SouthEast US 1311986
2000 March 3719014
2001 February Mid-Atlantic 428676
2001 February New England 615746
2001 February SouthEast US 618423
2001 February 1662845
2001 January Mid-Atlantic 610697
2001 January New England 509215
2001 January SouthEast US 379021
2001 January 1498933
2001 March Mid-Atlantic 637031
2001 March New England 566483
2001 March SouthEast US 655993
2001 March 1859507
15063855
25 rows selected.
This query converts the numeric month into the string expression of the name of the month and concatenates it with the string representation of the year. The same expression has to be used in the SELECT list and the ROLLUP clause. The expression TO_CHAR(O.YEAR)||' '||TO_CHAR(TO_DATE( O.MONTH,'MM'),'Month') is treated as one composite column. 12.5.3 Concatenated GroupingsWith Oracle9i, you can have multiple ROLLUP, CUBE, or GROUPING SETS operations, or a combination of these under the GROUP BY clause in a query. This is not allowed in Oracle8i. You will get an error message if you attempt the following query in Oracle8i: 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 ROLLUP (O.YEAR, O.MONTH), ROLLUP(R.NAME);
GROUP BY ROLLUP (O.YEAR, O.MONTH), ROLLUP(R.NAME)
*
ERROR at line 6:
ORA-30489: Cannot have more than one rollup/cube expression 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 ROLLUP (O.YEAR, O.MONTH), ROLLUP(R.NAME);
YEAR MONTH REGION TOTAL
---------- --------- -------------------- ----------
2000 January Mid-Atlantic 1221394
2000 January New England 1018430
2000 January SouthEast US 758042
2000 January 2997866
2000 February Mid-Atlantic 857352
2000 February New England 1231492
2000 February SouthEast US 1236846
2000 February 3325690
2000 March Mid-Atlantic 1274062
2000 March New England 1132966
2000 March SouthEast US 1311986
2000 March 3719014
2000 Mid-Atlantic 3352808
2000 New England 3382888
2000 SouthEast US 3306874
2000 10042570
2001 January Mid-Atlantic 610697
2001 January New England 509215
2001 January SouthEast US 379021
2001 January 1498933
2001 February Mid-Atlantic 428676
2001 February New England 615746
2001 February SouthEast US 618423
2001 February 1662845
2001 March Mid-Atlantic 637031
2001 March New England 566483
2001 March SouthEast US 655993
2001 March 1859507
2001 Mid-Atlantic 1676404
2001 New England 1691444
2001 SouthEast US 1653437
2001 5021285
Mid-Atlantic 5029212
New England 5074332
SouthEast US 4960311
15063855
36 rows selected.
When you have multiple grouping operations (ROLLUP, CUBE, or GROUPING SETS) in a GROUP BY clause, what you have is called a concatenated grouping. The result of the concatenated grouping is to produce a cross-product of groupings from each grouping operation. Therefore, the query: 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 ROLLUP(O.YEAR), ROLLUP (O.MONTH), ROLLUP (R.NAME); behaves as a CUBE and produces the same result as the query: 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 CUBE (O.YEAR, O.MONTH, R.NAME); Since a CUBE contains aggregates for all possible combinations of the grouping columns, the concatenated grouping of CUBES is no different from a regular CUBE, and all the following queries return the same result as the query shown previously. 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 CUBE (O.YEAR, O.MONTH), CUBE (R.NAME); 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 CUBE (O.YEAR), CUBE (O.MONTH, R.NAME); 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 CUBE (O.YEAR, O.MONTH), CUBE (O.YEAR, R.NAME); 12.5.3.1 Concatenated groupings with GROUPING SETSConcatenated groupings come in handy while using GROUPING SETS. Since GROUPING SETS produces only the subtotal rows, you can specify just the aggregation levels you want in your output by using a concatenated grouping of GROUPING SETS. The concatenated grouping of GROUPING SETS (a,b) and GROUPING SETS (c,d) will produce aggregate rows for the aggregation levels (a,c), (a,d), (b,c), and (b,d). The concatenated grouping of GROUPING SETS (a,b) and GROUPING SETS (c) will produce aggregate rows for the aggregation levels (a,c) and (b,c). For example: 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 GROUPING SETS (O.YEAR, O.MONTH), GROUPING SETS (R.NAME);
YEAR MONTH REGION TOTAL
---------- --------- -------------------- ----------
2000 Mid-Atlantic 3352808
2000 New England 3382888
2000 SouthEast US 3306874
2001 Mid-Atlantic 1676404
2001 New England 1691444
2001 SouthEast US 1653437
January Mid-Atlantic 1832091
January New England 1527645
January SouthEast US 1137063
February Mid-Atlantic 1286028
February New England 1847238
February SouthEast US 1855269
March Mid-Atlantic 1911093
March New England 1699449
March SouthEast US 1967979
15 rows selected.
The concatenated grouping GROUP BY GROUPING SETS (O.YEAR, O.MONTH), GROUPING SETS (R.NAME) in this example produces rows for aggregate levels (O.YEAR, R.NAME) and (O.MONTH, R.NAME). Therefore, you see aggregate rows for (Year, Region) and (Month, Region) combinations in the output. The following example extends the previous query: 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 GROUPING SETS (O.YEAR, O.MONTH), GROUPING SETS (O.YEAR, R.NAME); 1: YEAR MONTH REGION TOTAL 2: ---------- --------- -------------------- ---------- 3: 2000 10042570 4: 2001 5021285 5: 2000 January 2997866 6: 2000 February 3325690 7: 2000 March 3719014 8: 2001 January 1498933 9: 2001 February 1662845 10: 2001 March 1859507 11: 2000 Mid-Atlantic 3352808 12: 2000 New England 3382888 13: 2000 SouthEast US 3306874 14: 2001 Mid-Atlantic 1676404 15: 2001 New England 1691444 16: 2001 SouthEast US 1653437 17: January Mid-Atlantic 1832091 18: January New England 1527645 19: January SouthEast US 1137063 20: February Mid-Atlantic 1286028 21: February New England 1847238 22: February SouthEast US 1855269 23: March Mid-Atlantic 1911093 24: March New England 1699449 25: March SouthEast US 1967979 23 rows selected. This example produces four grouping combinations. Table 12-1 describes the various grouping combinations produced by this query and references their corresponding row numbers in the output.
The GROUPING SETS operation is independent of the order of columns. Therefore, the following two queries will produce the same results as shown previously: 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 GROUPING SETS (O.YEAR, R.NAME), GROUPING SETS (O.YEAR, O.MONTH); 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 GROUPING SETS (O.MONTH, O.YEAR), GROUPING SETS (R.NAME, O.YEAR); It is permissible to have a combination of ROLLUP, CUBE, and GROUPING SETS in a single GROUP BY clause, as in the following example: 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 GROUPING SETS (O.MONTH, O.YEAR), ROLLUP(R.NAME), CUBE (O.YEAR); However, the output from such queries seldom makes any sense. You should carefully evaluate the need for such queries when you intend to write one. 12.5.3.2 ROLLUP and CUBE as arguments to GROUPING SETSUnlike the ROLLUP and CUBE operations, the GROUPING SETS operation can take a ROLLUP or a CUBE as its argument. As you have seen earlier, GROUPING SETS produces only subtotal rows. However, there are times when you may need to print the grand total along with the subtotals. In such situations, you can perform the GROUPING SETS operation on ROLLUP operations, as in the following example. 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 GROUPING SETS (ROLLUP (O.YEAR), ROLLUP (O.MONTH), ROLLUP(R.NAME));
YEAR MONTH REGION TOTAL
---------- --------- -------------------- ----------
2000 10042570
2001 5021285
January 4496799
February 4988535
March 5578521
Mid-Atlantic 5029212
New England 5074332
SouthEast US 4960311
15063855
15063855
15063855
11 rows selected.
Notice that this example produces the subtotals for each dimension, as expected from the regular GROUPING SETS operations. Also, it produces the grand total across all the dimensions. However, you get three identical grand-total rows. The grand-total rows are repeated because they are produced by each ROLLUP operation inside the GROUPING SETS. If you insist on only one grand-total row, you may use the DISTINCT keyword in the SELECT clause: SELECT DISTINCT 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 GROUPING SETS (ROLLUP (O.YEAR), ROLLUP (O.MONTH), ROLLUP(R.NAME));
YEAR MONTH REGION TOTAL
---------- --------- -------------------- ----------
2000 10042570
2001 5021285
February 4988535
January 4496799
March 5578521
Mid-Atlantic 5029212
New England 5074332
SouthEast US 4960311
15063855
9 rows selected.
Note that the DISTINCT keyword eliminated the duplicate grand-total rows. You can also eliminate duplicate rows by using the GROUP_ID function, as discussed in later in this chapter. If you are interested in subtotals and totals on composite dimensions, you can use composite or concatenated ROLLUP operations within GROUPING SETS, as in the following example: 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 GROUPING SETS (ROLLUP (O.YEAR, O.MONTH), ROLLUP(R.NAME));
YEAR MONTH REGION TOTAL
---------- --------- -------------------- ----------
2000 January 2997866
2000 February 3325690
2000 March 3719014
2000 10042570
2001 January 1498933
2001 February 1662845
2001 March 1859507
2001 5021285
Mid-Atlantic 5029212
New England 5074332
SouthEast US 4960311
15063855
15063855
13 rows selected.
This query generates subtotals for (YEAR, MONTH) combinations, subtotals for the REGION, subtotals for the YEAR, and the grand total. Note that there are duplicate grand-total rows because of the multiple ROLLUP operations within the GROUPING SETS operation. |
|
|
|