Oracle Group By ROLLUP

It would be safe to assume that most if not all of us are familiar with Oracle "Group By" clause. You can however use the ROLLUP function to enhance the grouping actions performed within your queries.
Lets take a look at a fairly complex Group By example and then re-write it using the Group By ROLLUP.
For example, if you want to print the monthly total sales for each region, you would probably execute the following query:

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;

REGION               MONTH     SUM(ORD.TOTAL_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

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

As expected, this report prints the total sales for each region and month combination. However, in a more complex application, you may also 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;

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

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

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

Mid-Atlantic                           18923298

New England                            19756923

Southeast US                           20605485

                                       59285706



40 rows selected.

The 1st 36 rows list the sales for each month for every region. The last 3 rows however list the total per region as well as the sum for all sales. Lets see how we can accomplish the same results using Group By ROLLUP:

Syntax:
SELECT  . . .
FROM  . . .
GROUP BY ROLLUP (ordered list of grouping COLUMNS);

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



40 rows selected.

Same results we got using only Group By but with a lot less code and of course a lot lower SQL cost.
If you want to generate subtotals for each month instead of for each region, all you need to do is change the order of columns in the ROLLUP operation.

GROUP BY ROLLUP (ord.month, reg.region); instead of GROUP BY ROLLUP (reg.region, ord.month);