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:

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);

The above is the detailed content of ROLLUP. For more information, please follow other related articles on the PHP Chinese website!