Oracle ROLLUP

Summary: in this tutorial, you will learn how to use the Oracle ROLLUP extension to generate reports that contain subtotals and totals.

Getting started with Oracle ROLLUP #

Consider the following orders and order_items tables in the sample database:

The following query returns the sales revenue by customers in the year of 2017. It calculates net values for the sales orders with the Shipped status and is in charge of a salesman.

SELECT
  customer_id,
  SUM(quantity * unit_price) amount
FROM
  orders
  INNER JOIN order_items USING (order_id)
WHERE
  status = 'Shipped'
  AND salesman_id IS NOT NULL
  AND EXTRACT(
    YEAR
    FROM
      order_date
  ) = 2017
GROUP BY
  customer_id
ORDER BY
  amount DESC;Code language: SQL (Structured Query Language) (sql)

Try it

To get the sum of the values in the amount column, you may come up with the following subquery:

SELECT
  SUM(amount)
FROM
  (
    SELECT
      customer_id,
      SUM(quantity * unit_price) amount
    FROM
      orders
      INNER JOIN order_items USING (order_id)
    WHERE
      status = 'Shipped'
      AND salesman_id IS NOT NULL
      AND EXTRACT(
        YEAR
        FROM
          order_date
      ) = 2017
    GROUP BY
      customer_id
  );Code language: SQL (Structured Query Language) (sql)

Try it

Oracle provides a better and faster way to calculate the grand total by using the ROLLUP as shown in the following query:

SELECT
   customer_id,
   SUM(quantity * unit_price) amount
FROM
   orders
INNER JOIN order_items USING (order_id)
WHERE
   status      = 'Shipped' AND 
   salesman_id IS NOT NULL AND 
   EXTRACT(YEAR FROM order_date) = 2017
GROUP BY
   ROLLUP(customer_id);
Code language: SQL (Structured Query Language) (sql)

Try it

In this query, we used the ROLLUP expression to calculate the grand total of sales amounts of the selected orders.

As shown clearly from the output, the row with a NULL value in the customer_id column denotes the grand total row. The amount column of the grand total row showed the sum of all amounts in the output.

Oracle ROLLUP syntax #

The ROLLUP is an extension of the GROUP BY clause. The ROLLUP calculates multiple levels of subtotals across a group of columns (or dimensions) along with the grand total.

The following illustrates the syntax of the ROLLUP :

SELECT
  col1,
  col2,
  aggregate (col3)
FROM
  table_name
GROUP BY
  ROLLUP (col1, col2);Code language: SQL (Structured Query Language) (sql)

Try it

In the query syntax above, the ROLLUP creates subtotals that roll up from the most detailed level to a grand total, following a grouping column specified in the ROLLUP.

The ROLLUP works as follows:

1. First, calculate the standard aggregate values in the GROUP BY clause.
2. Then, progressively create higher-level subtotals of the grouping columns, which are col2 and col1 columns, from right to left.
3. Finally, calculate the grand total.

The ROLLUP clause generates the number of grouping sets which is the same as the number of grouping columns specified in the ROLLUP plus a grand total. In other words, if you have n columns listed in the ROLLUP, you will get n+ 1 level of subtotals with ROLLUP.

In the syntax above, the ROLLUP clause generates the following grouping sets:

1. (col1, col2)
2. (col2)
3. (grand total)

The number of rows in the output is derived from the number of unique combinations of values in the grouping columns.

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

SELECT
   col1,
   col2,
   aggregate(col3)
FROM
   table_name
GROUP BY
   col1,
   ROLLUP (col2);
Code language: SQL (Structured Query Language) (sql)

More Oracle ROLLUP examples #

The following example use ROLLUP to return the sales values by salesman and customer:

SELECT
  salesman_id,
  customer_id,
  SUM(quantity * unit_price) amount
FROM
  orders
  INNER JOIN order_items USING (order_id)
WHERE
  status = 'Shipped'
  AND salesman_id IS NOT NULL
  AND EXTRACT(
    YEAR
    FROM
      order_date
  ) = 2017
GROUP BY
  ROLLUP (salesman_id, customer_id);Code language: SQL (Structured Query Language) (sql)

Try it

As you can see from the output, the query returned the following set of rows:

• The regular aggregation rows that would be returned by the GROUP BY clause without using the ROLLUP expression.
• The first level of subtotals aggregating across salesman for each combination of salesman and customer.
• The second-level subtotals aggregating across salesman and customer for each salesman.
• A grand total row.

The following query performs a partial rollup:

SELECT
  salesman_id,
  customer_id,
  SUM(quantity * unit_price) amount
FROM
  orders
  INNER JOIN order_items USING (order_id)
WHERE
  status = 'Shipped'
  AND salesman_id IS NOT NULL
  AND EXTRACT(
    YEAR
    FROM
      order_date
  ) = 2017
GROUP BY
  salesman_id,
  ROLLUP (customer_id);Code language: SQL (Structured Query Language) (sql)

Try it

The query outputs regular aggregation rows that would be returned by the GROUP BY clause without using the ROLLUP and the subtotals aggregating across salesman for each combination of salesman and customer.

In this tutorial, you have learned how to use the Oracle ROLLUP to generate reports that contain subtotals and totals.