Oracle ROLLUP

Summary: in this tutorial, you’ll learn how to use the Oracle ROLLUP expression to automatically generate subtotals and grand totals for grouped rows.

Introduction to Oracle ROLLUP expression #

In Oracle, the GROUP BY clause groups rows into summary rows. The GROUP BY clause may include a ROLLUP expression that automatically calculates subtotals and grand totals for summary rows.

Here’s the basic syntax of the  ROLLUP expression:

SELECT
  column1,
  column2,
  aggregate (column3)
FROM
  table_name
GROUP BY
  ROLLUP (column1, column2);Code language: SQL (Structured Query Language) (sql)

The ROLLUP expression generates subtotals moving from right to left in the column list, ending in a grand total.

The ROLLUP expression follows a hierarchical pattern:

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

The ROLLUP works as follows:

• First, calculate the aggregate values in the GROUP BY clause.
• Second, progressively create higher-level subtotals of the grouping columns, which are column2 and column1 columns, from right to left.
• Third, calculate the grand total.

If the ROLLUP expression has n columns, it’ll generate n+ 1 level of subtotals. For example, if the ROLLUP expression includes two columns, it’ll generate three grouping sets:

1. (column1, column2)
2. (column2)
3. (grand total)

Oracle ROLLUP expression example #

Suppose we have the following customer_category_sales view:

The following statement uses the ROLLUP to calculate the subtotals and grand totals of sales amount:

SELECT
  category,
  customer,
  sum(sales_amount) total_sales_amount
FROM
  customer_category_sales
GROUP BY
  ROLLUP (category, customer)
ORDER BY
  category nulls last,
  customer nulls last;Code language: SQL (Structured Query Language) (sql)

Try it

The ROLLUP(category, customer) group rows hierarchically:

• Group by category and customer (regular rows).
• Group by category only (subtotals per category where the customer is NULL).
• Grand total (category and customer are NULL).

Output:

Partial rollup #

To reduce the number of subtotals, you can perform a partial roll-up as shown in the following syntax:

SELECT
  column1,
  column2,
  aggregate (column3)
FROM
  table_name
GROUP BY
  column1,
  ROLLUP (column2);Code language: SQL (Structured Query Language) (sql)

For example:

SELECT
  category,
  customer,
  sum(sales_amount) total_sales_amount
FROM
  customer_category_sales
GROUP BY
  customer,
  ROLLUP (category);Code language: SQL (Structured Query Language) (sql)

Try it

Output:

In this example:

• ROLLUP(category) with customer outside the rollup gives subtotals per customer.
• No grand total.

Summary #

• Use the ROLLUP expression to generate reports with subtotals and grand totals.

Quiz #