MySQL ROLLUP

MySQL ROLLUP

The ROLLUP operator in MySQL is used with the GROUP BY clause to generate subtotals and grand totals for grouped data. It provides a convenient way to calculate cumulative totals and summarize results at multiple levels within a single query.

Key Features

1. Subtotals: Provides subtotals for each group.
2. Grand Total: Adds a row that shows the grand total for all groups.
3. Works with GROUP BY: Used as part of the GROUP BY clause.

Syntax

SELECT column1, column2, ..., aggregate_function(column3)
FROM table_name
GROUP BY column1, column2 WITH ROLLUP;

• column1, column2: Columns to group by.
• aggregate_function(column3): Functions like SUM, COUNT, etc., applied to the grouped data.
• WITH ROLLUP: Generates subtotal and grand total rows.

Example Table

Table: sales

+----+---------+--------+-------+
| id | product | region | sales |
+----+---------+--------+-------+
| 1  | Laptop  | North  | 500   |
| 2  | Laptop  | South  | 300   |
| 3  | Phone   | North  | 200   |
| 4  | Phone   | South  | 100   |
| 5  | Tablet  | North  | 400   |
| 6  | Tablet  | South  | 600   |
+----+---------+--------+-------+

1. Basic ROLLUP Example

Calculate total sales for each product and a grand total:

SELECT product, SUM(sales) AS total_sales
FROM sales
GROUP BY product WITH ROLLUP;

Result

+---------+-------------+
| product | total_sales |
+---------+-------------+
| Laptop  | 800         |
| Phone   | 300         |
| Tablet  | 1000        |
| NULL    | 2100        | -- Grand Total
+---------+-------------+

• The NULL in the product column represents the grand total.

2. ROLLUP with Multiple Columns

Generate subtotals by region, product, and grand total:

SELECT region, product, SUM(sales) AS total_sales
FROM sales
GROUP BY region, product WITH ROLLUP;

Result

+--------+---------+-------------+
| region | product | total_sales |
+--------+---------+-------------+
| North  | Laptop  | 500         |
| North  | Phone   | 200         |
| North  | Tablet  | 400         |
| North  | NULL    | 1100        | -- Subtotal for North
| South  | Laptop  | 300         |
| South  | Phone   | 100         |
| South  | Tablet  | 600         |
| South  | NULL    | 1000        | -- Subtotal for South
| NULL   | NULL    | 2100        | -- Grand Total
+--------+---------+-------------+

• NULL in product: Subtotal for each region.
• NULL in both region and product: Grand total.

3. Filtering Out ROLLUP Rows

To exclude rows with NULL values (subtotal or grand total rows), use the HAVING clause:

SELECT region, product, SUM(sales) AS total_sales
FROM sales
GROUP BY region, product WITH ROLLUP
HAVING region IS NOT NULL AND product IS NOT NULL;

4. ROLLUP with Aliases

You can rename NULL values in the results for better readability:

SELECT 
    IFNULL(region, 'Total') AS region,
    IFNULL(product, 'Subtotal') AS product,
    SUM(sales) AS total_sales
FROM sales
GROUP BY region, product WITH ROLLUP;

Result

+--------+----------+-------------+
| region | product  | total_sales |
+--------+----------+-------------+
| North  | Laptop   | 500         |
| North  | Phone    | 200         |
| North  | Tablet   | 400         |
| North  | Subtotal | 1100        |
| South  | Laptop   | 300         |
| South  | Phone    | 100         |
| South  | Tablet   | 600         |
| South  | Subtotal | 1000        |
| Total  | Subtotal | 2100        |
+--------+----------+-------------+

5. Using ROLLUP with Multiple Aggregates

Calculate both the SUM and COUNT for each group:

SELECT region, product, COUNT(*) AS count, SUM(sales) AS total_sales
FROM sales
GROUP BY region, product WITH ROLLUP;

Result

+--------+---------+-------+-------------+
| region | product | count | total_sales |
+--------+---------+-------+-------------+
| North  | Laptop  | 1     | 500         |
| North  | Phone   | 1     | 200         |
| North  | Tablet  | 1     | 400         |
| North  | NULL    | 3     | 1100        |
| South  | Laptop  | 1     | 300         |
| South  | Phone   | 1     | 100         |
| South  | Tablet  | 1     | 600         |
| South  | NULL    | 3     | 1000        |
| NULL   | NULL    | 6     | 2100        |
+--------+---------+-------+-------------+

Performance Tips

1. Indexes:

   • Ensure columns used in the GROUP BY clause are indexed for optimal performance.

2. Filtering with ROLLUP:

   • Use HAVING or WHERE clauses to control the output rows.

Use Cases

1. Sales Reporting:

   • Subtotals and grand totals by region, product, or other dimensions.

2. Inventory Management:

   • Calculate stock levels and subtotals across categories.

3. Financial Summaries:

   • Generate income and expense summaries for each department and overall.

Let me know if you need help with additional examples or queries!