Ask Question
Help Others
Home › SQL & PL/SQL

Oracle Group By Grouping Sets

Thu, 01/10/2008 - 7:34pm — Lepa
Your rating: None Average: 3.5 (2 votes)

In one of my previous ROLLUP posts I showed you how to generate summary information. However, the output of ROLLUP includes the rows produced by the regular GROUP BY operation along with the summary rows.

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.

As you can see, the output above contains ONLY the subtotals at region, month and year levels.

Bookmark/Search this post with:
  • Delicious
  • Digg
  • StumbleUpon
  • Reddit
  • Furl
  • Facebook
  • Google
  • Yahoo
  • Technorati
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.
Tue, 09/02/2008 - 2:44am
#1
Guest's picture
Guest (not verified)
Re: Oracle Group By Grouping Sets

And what if I'm interested in totals but I want only one row for different years?

for example:

YEAR JAN FEB MAR
------ ----------- ----------- ------------
2000 4496799 4988535 5578521
2001 5029212 5074332 4960311

Top
  • reply
Tue, 09/02/2008 - 8:39am
#2
Lepa's picture
Lepa
User offline. Last seen 2 days 11 hours ago. Offline
Joined: 06/23/2008
Posts: 479
Re: Oracle Group By Grouping Sets

>> but I want only one row for different years
You can get the total for each month in every year by grouping by year and month. But you will not have one and only one row for each year. To accomplish what you want, you need to look at how to convert rows into columns. Here is a tip on how to do that posted at Oracle's site
http://www.oracle.com/technology/oramag/code/tips2004/050304.html

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Top
  • reply
Thu, 04/09/2009 - 7:56am
#3
Guest's picture
Guest (not verified)
Re: Oracle Group By Grouping Sets

select the employee using group by function where department member is greater than 4

Plz help me on the matter

Top
  • reply
Sat, 07/24/2010 - 5:08pm
#4
Guest's picture
Guest (not verified)
Re: Oracle Group By Grouping Sets

Hello Lepa,

I have been looking for the sample database that contains the orders and region tables you used in this example without success.

I am using 11gr2 and I believe I installed all the sample schemas. Where would I find the tables/data you've been using in your examples ?

Thank you,

John.

Top
  • reply

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
13 + 2 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Unanswered Forum Posts

Title Under Posted on
How to run nvision report through portal PeopleSoft Technical 08/31/2010 - 4:45am
Any1 here who can help? PeopleSoft Technical 08/31/2010 - 2:12am
How to create matrix layout PeopleSoft Technical 08/27/2010 - 2:22am
UnCaught C++ Exception PeopleSoft Technical 08/10/2010 - 9:05am
more