Oracle Group By Grouping Sets

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

     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.

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