Introducing to Oracle GROUPING SETS by Practical Examples

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)

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)

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)

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 set may include zero columns. 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)

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 to return the same number of columns. Therefore, to make it work, 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:

This query has two main issues:

It is so lengthy that is difficult to read and maintain.
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:

In this output, rows whose columns 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 distinguish 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)

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 grouping-related functions such as GROUPING() and GROUPING_ID() to handle grouping sets.

Was this tutorial helpful?