Oracle Group By ROLLUP

Your rating: None Average: 4.5 (57 votes)

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);

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Kestas's picture
Kestas (not verified)
Re: Oracle Group By ROLLUP

wow.. rollup is really cool

fm's picture
fm (not verified)
Re: Oracle Group By ROLLUP

rollup makes it easy but how do you format the total generated by rollup eg $99,999.00

Guest's picture
Guest (not verified)
Re: Oracle Group By ROLLUP

If, I want to calculate ytd ( year to date calculation ),
then i have to sum up monthly then sum up for yearly.
Eg :
--------------------------------------
CURR GL_POST PROD AMNT YTD_CALC
--------------------------------------
525 474094 153383 0.51 0.51
525 474094 153383 0.51 1.02
525 474094 153383 13.91 14.93
525 474094 153383 47.79 62.72
525 474094 153383 1.58 64.3
525 474094 153383 7.41 71.71
525 474094 153383 2.44 74.15
525 474094 153383 8.36 82.51
525 474094 153383 7.47 89.98
525 474094 153383 42.32 132.3
525 474094 153383 43.53 175.83
525 474094 153383 0.51 176.34
525 474094 153383 -43.53 132.81
--------------------------------------
how can i do this ytd_calc using rollup or group by clauses (i.e., i have to sum up for every row) ?

Raja's picture
Raja (not verified)
Re: Oracle Group By ROLLUP

Sorry, I have provided wrong example.
Please consider the following example.
I have to calculate YTD as follows :

GL Curr Day Amount Month YTD
5805 45454 1-Jan 5.23 5.23 5.23
5805 45454 2-Jan -4.52 0.71 5.94
5805 45454 3-Jan 25.3 26.01 31.95
5805 45454 4-Jan 10.53 36.54 68.49
5805 45454 5-Jan -1.88 34.66 103.15

Guest's picture
Guest (not verified)
Re: Oracle Group By ROLLUP

How can we give caption to grand_Total value in Rollup

Jarek's picture
Jarek (not verified)
Re: Oracle Group By ROLLUP

You can use nvl() function. In this case something like that should work:

SELECT nvl(reg.region,'All regions'),
   nvl(TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month'),'All months') month, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY ROLLUP (reg.region, ord.month);
Danny's picture
Danny (not verified)
Re: Oracle Group By ROLLUP

don't use NVL(), if you have a null value as region, it will error.
use grouping() & decode().
e.g

SELECT decode(grouping(reg.region),1, 'All regions', reg.region) REGION,
decode(grouping(ord.month),1, 'All months', 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);

manoj's picture
manoj (not verified)
Re: Oracle Group By ROLLUP

ya its very cool to have a Rollup
it reduces the programmer work by adding minimal overhead to select
i really like it

Irene's picture
Irene (not verified)
Re: Oracle Group By ROLLUP

Wow this was exactly what I was looking for... after 3 developers told me there was no way to easily add a total row to a grouped select statement. The decode/grouping solution works great for labels also - love it!

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
17 + 1 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.