ROLLUP

Understanding the Use of the ROLLUP Function in SQL

The ROLLUP function is a powerful tool in SQL that allows you to generate subtotals and grand totals in aggregated queries. This feature is particularly useful for reports and analysis, where there is a need to calculate consolidated values at different levels of detail.

What is ROLLUP?

The ROLLUP function is used in conjunction with the GROUP BY clause to create hierarchical groupings. It automatically adds extra rows that represent subtotals (partial sums) and the grand total at the end of the query.

Basic Syntax

SELECT column1, column2, aggregation(column3)
FROM table
GROUP BY ROLLUP(column1, column2);

How Does it Work?

• ROLLUP processes the columns specified in the GROUP BY clause in hierarchical order.
• It starts by grouping all values for each more detailed level, then progressively moves to less detailed levels.
• Finally, it adds a row with the grand total.

Practical Example

Let's work with the products table created in the code above, which contains information about products and their categories.

1. Normal Query

If we query only the grouping by category, we get the following result:

SELECT product_category, SUM(product_value) AS total
FROM products
GROUP BY product_category;

Result:

2. Query with ROLLUP

By adding the ROLLUP function, we include the subtotals and the grand total:

SELECT CASE
         WHEN product_category IS NULL THEN 'Grand Total'
         ELSE product_category
       END AS category,
       SUM(product_value) AS total
FROM products
GROUP BY ROLLUP(product_category);

Result:

3. Subtotals with Multiple Levels

We can go further and calculate subtotals at multiple levels. For example, to calculate subtotals by category and product:

SELECT CASE
         WHEN product_category IS NULL AND product_name IS NULL THEN 'Grand Total'
         WHEN product_name IS NULL THEN 'Subtotal - ' || product_category
         ELSE product_name
       END AS description,
       SUM(product_value) AS total
FROM products
GROUP BY ROLLUP(product_category, product_name);

Result:

When to Use ROLLUP?

• Reports that require consolidated summaries.
• Scenarios where you want automation of total calculations without needing to write additional queries.
• Hierarchical data that needs analysis at different levels of granularity.

The ROLLUP function simplifies the creation of complex reports, reducing the need for manual manipulation. This functionality is supported by many databases such as Oracle, SQL Server, and MySQL. Use it to make your queries more efficient and your reports more dynamic! 🚀