How to Use grouping sets for GROUPING and customization in Oracle databases?

The following article mainly introduces the implementation of GROUPING custom summary in Oracle grouping sets. This article mainly uses the author's experience in practical operations, to describe the correct use of grouping sets grouping customization in Oracle databases.

When you use group by statements with Aggregate functions such as COUNT and SUM, you generally cannot get the total number of levels. In group by, each unique column combination generates a total number, but these total numbers are not "accumulated" into the total number at a higher level.

To achieve this, you can use group by rollup or group by cube to replace group by. However, they will generate all possible total numbers, but you may not need all of them. For group by cube, 2 ^ n groups are generated, where n is the number of columns in group.

View the following query, which uses the SH sample mode:

 
 

  
  
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) 
 
 

This will generate the total number of eight groups:

Total of all rows

Each channel includes all products and customers.

Each customer, including all products and channels.

Each product, including all customers and channels.

Each channel/customer combination, including all products.

Each channel/product portfolio, including all customers.

Each product/customer combination, including all channels.

A combination of products, customers, and channels.

There may be many combinations. Each added column in the group by cube doubles the total number.

You can use group by grouping sets instead of group by cube. You can use the application to specify the total number of groups you are interested in. Because it does not have to calculate it, it does not need a set or produce too many results), so it is more efficient for the SQL engine.

The format is:

 
 

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

List) is a column sequence in parentheses. This combination generates a total number. To add a sum, you must add a (NUlL) grouping set.

For example, if you only need to generate the total number of each product including all customers and channels) and each customer/channel combination including all products, you can enter:

 
 

  
  
SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)  
  
  
FROM sales  
  
  
WHERE cust_id < 3 
  
  
GROUP BY GROUPING SETS (  
  
  
(prod_id), (cust_id, channel_id)  
  
  
); 
 
 

This method reduces the total number of datasets generated from 180 to 37, and helps you focus on answering your questions. The above content is a summary of Oracle grouping sets for GROUPING and customization. I hope you will gain some benefits.