At normal time, you may want to have the subtotal for each region over all months, along with the total for all regions, or you may want the subtotal for each month over all regions, along with the total for all months. In short, you may need to generate subtotals and totals at more than one level.
Lets see how you go about doing this without using ROLLUP.
SELECT reg.region, TO_CHAR (TO_DATE (ord.month, 'MM' ), 'Month' ) month, SUM (ord.total_sales ) FROM orders ord, region reg WHERE ord.region_id = reg.region_id GROUP BY reg.region, ord.month; UNION ALL SELECT reg.region, NULL, SUM (ord.total_sales ) FROM orders ord, region reg WHERE ord.region_id = reg.region_id GROUP BY reg.region UNION ALL SELECT NULL, NULL, SUM (ord.total_sales ) FROM orders ord, region reg WHERE ord.region_id = reg.region_id;
Lets see how we can accomplish the same results using Group By ROLLUP:
SELECT reg.region, TO_CHAR (TO_DATE (ord.month, 'MM' ), 'Month' ) month, SUM (ord.total_sales ) FROM orders ord, region reg WHERE ord.region_id = reg.region_id GROUP BY ROLLUP (reg.region, ord.month );
REGION MONTH SUM(ORD.TOT_SALES)
-------------------- --------- ---------------- New England January 1527645 New England February 1847238 New England March 1699449 New England April 1792866 New England May 1698855 New England June 1510062 New England July 1678002 New England August 1642968 New England September 1726767 New England October 1648944 New England November 1384185 New England December 1599942 New England 19756923 Mid-Atlantic January 1832091 Mid-Atlantic February 1286028 Mid-Atlantic March 1911093 Mid-Atlantic April 1623438 Mid-Atlantic May 1778805 Mid-Atlantic June 1504455 Mid-Atlantic July 1820742 Mid-Atlantic August 1381560 Mid-Atlantic September 1178694 Mid-Atlantic October 1530351 Mid-Atlantic November 1598667 Mid-Atlantic December 1477374 Mid-Atlantic 18923298 Southeast US January 1137063 Southeast US February 1855269 Southeast US March 1967979 Southeast US April 1830051 Southeast US May 1983282 Southeast US June 1705716 Southeast US July 1670976 Southeast US August 1436295 Southeast US September 1905633 Southeast US October 1610523 Southeast US November 1661598 Southeast US December 1841100 Southeast US 20605485 59285706