Oracle Group By ROLLUP-SubTotal

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

转载于:https://www.cnblogs.com/GoDevil/archive/2008/08/11/1265328.html