Oracle CUBE

Summary: in this tutorial, you’ll learn how to use the Oracle CUBE expression to generate grouping sets for all possible combinations of dimensions.

Introduction to Oracle CUBE expression #

In Oracle, CUBE is an expression in the GROUP BY clause that allows you to generate grouping sets for all possible combinations of dimensions.

Here’s the basic syntax of the CUBE with two columns (or dimensions):

SELECT
  column_1,
  column_2,
  aggregate (column_3)
FROM
  table_name
GROUP BY
  CUBE (column_1, column_2);Code language: SQL (Structured Query Language) (sql)

In this syntax, the CUBE expression generates grouping sets of all combinations of column_1 and column_2 dimensions, which returns 4 grouping sets.

In general, the CUBE will generate 2ⁿ grouping sets if you have n columns specified in the CUBE expression.

For example, when the aggregate function is the SUM() function, you will have 2ⁿ subtotals for all the possible combinations of dimensions.

The column1 and column_2 are called dimensions. The result of the aggregate(column_2) aggregate function is known as a fact.

Typically, a fact is a number such as sales amount. A dimension gives the fact a business context. For example, the product category and customer columns are dimensions that describe the sales amount by product category or customer.

Oracle CUBE expression example #

Suppose we have the following customer_category_sales view that includes four product categories and two customers:

The following example uses the CUBE expression in the GROUP BY clause to generate subtotals for categories and customers and grand total for all customers and categories:

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

Try it

Output:

In this example, we have two dimensions: category and customer, therefore, the statement returns 4 subtotals:

• A subtotal by category.
• A subtotal by customer.
• A subtotal by both category and customer.
• A grand total.

The rows with NULL indicate that they are the subtotal rows, not traditional NULL.

Partial cube #

Oracle allows you to reduce the number of generated grouping sets by using a partial cube as shown in the following syntax:

SELECT
  column_1,
  column_2,
  aggregate (column_3)
FROM
  table_name
GROUP BY
  column_1, CUBE (column_2);Code language: SQL (Structured Query Language) (sql)

In this case, you will get 2 instead of 4 grouping sets.

For example, the following query uses a partial cube that generates subtotals for the product category dimension only:

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

Try it

Here is the output:

Summary #

• Use the Oracle CUBE expression in the GROUP BY clause to generate grouping sets for all possible combinations of dimensions.