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:

category	customer	sales_amount
CPU	Plains GP Holdings	746077.25
CPU	Raytheon	1217842.73
Mother Board	Plains GP Holdings	150418.39
Mother Board	Raytheon	258828.52
Storage	Plains GP Holdings	336014.38
Storage	Raytheon	486325.00
Video Card	Plains GP Holdings	1055198.31
Video Card	Raytheon	815087.32

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:

category	customer	total_sales_amount
CPU	Plains GP Holdings	746077.25
CPU	Raytheon	1217842.73
CPU	NULL	1963919.98 ← subtotal for CPU
Mother Board	Plains GP Holdings	150418.39
Mother Board	Raytheon	258828.52
Mother Board	NULL	409246.91 ← subtotal
Storage	Plains GP Holdings	336014.38
Storage	Raytheon	486325.00
Storage	NULL	822339.38 ← subtotal
Video Card	Plains GP Holdings	1055198.31
Video Card	Raytheon	815087.32
Video Card	NULL	1870285.63 ← subtotal
NULL	NULL	5062791.90 ← grand total

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:

category	customer	total_sales_amount
CPU	Plains GP Holdings	746077.25
Mother Board	Plains GP Holdings	150418.39
Storage	Plains GP Holdings	336014.38
Video Card	Plains GP Holdings	1055198.31
NULL	Plains GP Holdings	2286738.33 ← subtotal per customer
CPU	Raytheon	1217842.73
Mother Board	Raytheon	258828.52
Storage	Raytheon	486325.00
Video Card	Raytheon	815087.32
NULL	Raytheon	2778083.57 ← subtotal per customer

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 #