Group by GROUPING SETS for custom rollups in Oracle

The GROUPING SETS option of GROUP BY lets you generate custom sets of subtotals, without seeing as many rows of output as GROUP BY CUBE. In this tip, Bob Watkins offers code samples of how to use GROUPING SETS.

When you use GROUP BY with the summary functions like COUNT and SUM, you normally don't get multi-level totals. Each unique combination of the columns in the GROUP BY generates a total, but these totals aren't "rolled up" into higher level totals.

To do so, you can change the GROUP BY to a GROUP BY ROLLUP or GROUP BY CUBE, but that will generate every possible combination of totals, and you may not want all of them. For GROUP BY CUBE, there will be 2^n sets of totals, where n is the number of columns in the GROUP BY.

Consider the following query, which uses the SH sample schema:

SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)
FROM sales
WHERE cust_id < 3
GROUP BY CUBE (prod_id, cust_id, channel_id)
Weekly Oracle tips in your inbox
TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more.
Automatically sign up today!

This will generate eight sets of totals:

  • A grand total of all rows
  • For each channel, with all products and customers
  • For each customer, with all products and channels
  • For each product, with all customers and channels
  • For each channel/customer combination, with all products
  • For each channel/product combination, with all customers
  • For each product/customer combination, with all channels
  • For each combination of product, customer, and channel

That's a lot of totals! And it goes up by a power of 2 with each column you add to the GROUP BY CUBE list.

GROUP BY GROUPING SETS is an alternative to GROUP BY CUBE. It lets you specify just the combinations of totals you're interested in. It's more efficient for the SQL Engine because it doesn't have to compute aggregations it doesn't need (and it's more efficient for you to not be buried in output).

The format is:

GROUP BY GROUPING SETS (  (list), (list) ... )

where (list) is a list of columns in parentheses, the combination of which is to generate a total. To add a grand total, add (NULL) as one of the grouping sets.

For example, to see just the totals for each product (all customers and channels) and each customer/channel combination (all products), you would type:

SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)
FROM sales
WHERE cust_id < 3
   (prod_id), (cust_id, channel_id)

This approach reduces the number of totals produced from 180 to 37 over this data set, and helps you focus on answering the questions you want answered.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

Editor's Picks