Oracle GROUPING SETS

Summary: in this tutorial, you will learn about grouping set concept and how to use the Oracle GROUPING SETS expression to generate multiple grouping sets in a query.

Setting up a sample view

Let’s create a view that returns sales amounts by product category and customer. For the demonstration purpose, we will pick only two customers whose identities are 1 and 2.

Here is the statement for creating the view:

CREATE VIEW customer_category_sales AS SELECT category_name category, customers.name customer, SUM(quantity*unit_price) sales_amount FROM orders INNER JOIN customers USING(customer_id) INNER JOIN order_items USING (order_id) INNER JOIN products USING (product_id) INNER JOIN product_categories USING (category_id) WHERE customer_id IN (1,2) GROUP BY category_name, customers.name;
Code language: SQL (Structured Query Language) (sql)

This query returns data from the customer_category_sales view:

SELECT customer, category, sales_amount FROM customer_category_sales ORDER BY customer, category;
Code language: SQL (Structured Query Language) (sql)
oracle grouping sets

Introduction to the grouping set concept

A grouping set is a grouping of one or more columns by which you group using the GROUP BY clause. A grouping set is denoted by a list of comma-separated columns in parentheses:

(column1, column2,...)
Code language: SQL (Structured Query Language) (sql)

For example, this query returns a grouping set that includes the category column, (category) grouping set:

SELECT category, SUM(sales_amount) FROM customer_category_sales GROUP BY category;
Code language: SQL (Structured Query Language) (sql)
oracle grouping sets - category

The following query defines another grouping set that includes the customer column, or (customer) grouping set:

SELECT customer, SUM(sales_amount) FROM customer_category_sales GROUP BY customer;
Code language: SQL (Structured Query Language) (sql)
oracle grouping sets - customer

And this query returns a grouping set that includes both columns customer and category, or (customer, category) grouping set:

SELECT customer, category, sales_amount FROM customer_category_sales ORDER BY customer, category;
Code language: SQL (Structured Query Language) (sql)
oracle grouping sets - customer and category

A grouping sets may include zero column. In this case, it is an empty grouping set, which is denoted by (). The following query doesn’t use the GROUP BY clause, therefore, it returns an empty grouping set ():

SELECT SUM(sales_amount) FROM customer_category_sales;
Code language: SQL (Structured Query Language) (sql)
oracle grouping sets - empty grouping set

So far, we have four queries that return 4 grouping sets: (category), (customer), (category, customer), and ().

If you want to return four grouping sets in one query, you need to use the UNION ALL operator.

However, the UNION ALL operator requires all involved queries return the same number of columns. Therefore, to make it works, you need to add NULL to the select list of each query as shown in the following query:

SELECT category, NULL, SUM(sales_amount) FROM customer_category_sales GROUP BY category UNION ALL SELECT customer, NULL, SUM(sales_amount) FROM customer_category_sales GROUP BY customer UNION ALL SELECT customer, category, sum(sales_amount) FROM customer_category_sales GROUP BY customer, category UNION ALL SELECT NULL, NULL, SUM(sales_amount) FROM customer_category_sales;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

oracle grouping sets - UNION ALL

This query has two main issues:

  1. It is so lengthy that make it difficult to read and maintain.
  2. Its performance is not optimal because Oracle has to execute 4 queries separately first and then combine all the immediate result sets into a single one.

This is why Oracle introduced the GROUPING SETS expression to cope with these issues.

Oracle GROUPING SETS expression

A GROUPING SETS expression allows you to selectively define one or more grouping sets in a query. Here is the syntax of the GROUPING SETS expression:

GROUP BY GROUPING SETS(grouping_set_list);
Code language: SQL (Structured Query Language) (sql)

In this syntax, the grouping_set_list is a list of comma-separated grouping sets, for example:

GROUP BY GROUPING SETS( (), (c1), (c2), (c1,c2), (c1,c2,c3) )
Code language: SQL (Structured Query Language) (sql)

In this example, we have five grouping sets: (), (c1). (c2), (c1,c2), and (c1,c2,c3).

Back to our query example that uses the UNION ALL operators above, you can use the GROUPING SETS instead:

SELECT customer, category, SUM(sales_amount) FROM customer_category_sales GROUP BY GROUPING SETS( (customer,category), (customer), (category), () ) ORDER BY customer, category;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

oracle grouping sets example

In this output, rows whose column have NULL are super-aggregate rows. For example, the row number 5, 10 and 15.

Oracle GROUPING() function

The GROUPING() function differentiates the super-aggregate rows from regular grouped rows. The following illustrates the basic syntax of the GROUPING() function:

GROUPING(expression)
Code language: SQL (Structured Query Language) (sql)

The expression must match with the expression in the GROUP BY clause.

The GROUPING() function returns a value of 1 when the value of expression in the row is NULL representing the set of all values. Otherwise, it returns 0.

This query uses the GROUPING() function to distinguishes super-aggregate rows from the regular grouped rows:

SELECT customer, category, GROUPING(customer) customer_grouping, GROUPING(category) category_grouping, SUM(sales_amount) FROM customer_category_sales GROUP BY GROUPING SETS( (customer,category), (customer), (category), () ) ORDER BY customer, category;
Code language: SQL (Structured Query Language) (sql)
oracle grouping function example

To make the output more readable, you can combine the DECODE() function with the GROUPING() function as shown in the following query:

SELECT DECODE(GROUPING(customer),1,'ALL customers', customer) customer, DECODE(GROUPING(category),1,'ALL categories', category) category, SUM(sales_amount) FROM customer_category_sales GROUP BY GROUPING SETS( (customer,category), (customer), (CATEGORY), () ) ORDER BY customer, category;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

oracle grouping sets - with DECODE function example

Oracle GROUPING_ID() function

The GROUPING_ID() function takes the “group by” columns and returns a number denoting the GROUP BY level. In other words, it provides another compact way to identify the subtotal rows.

This statement uses the GROUPING_ID() function to return the GROUP BY level:

SELECT customer, category, GROUPING_ID(customer,category) grouping, SUM(sales_amount) FROM customer_category_sales GROUP BY GROUPING SETS( (customer,category), (customer), (category), () ) ORDER BY customer, category;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

oracle grouping sets - GROUPING_ID function example

In this tutorial, you have learned how to use the Oracle GROUPING SETS expression to generate one or more grouping sets in a query. You also learned how to use the grouping related functions such as GROUPING() and GROUPING_ID() to handle grouping sets.

Was this tutorial helpful?