So, what if you are only interested in totals. Well, Oracle does provide an extension to the GROUP BY clause called GROUPING SETS that you can use to generate summary information at the level you choose without including all the rows produced by the regular GROUP BY operation.
Lets take a look at an example:
SELECT ord.year,
TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month,
reg.region,
SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
AND ord.month BETWEEN 1 AND 3
GROUP BY GROUPING SETS (ord.year, ord.month, reg.region);
TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month,
reg.region,
SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
AND ord.month BETWEEN 1 AND 3
GROUP BY GROUPING SETS (ord.year, ord.month, reg.region);
YEAR MONTH REGION SUM(O.TOT_SALES)
---------- --------- -------------------- ----------------
Mid-Atlantic 5029212
New England 5074332
Southeast US 4960311
January 4496799
February 4988535
March 5578521
2000 10042570
2001 5021285
8 rows selected.
---------- --------- -------------------- ----------------
Mid-Atlantic 5029212
New England 5074332
Southeast US 4960311
January 4496799
February 4988535
March 5578521
2000 10042570
2001 5021285
8 rows selected.