ROLLUP for subtotals in MySQL - Step-by-Step Execution

Concept Flow - ROLLUP for subtotals

Start with GROUP BY columns

↓

Aggregate rows by groups

↓

Add subtotal rows for each grouping level

↓

Add grand total row

↓

Return result with subtotals and total

ROLLUP groups rows and adds extra rows for subtotals and a grand total automatically.

Execution Sample

MySQL

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

This query sums sales by category and product, then adds subtotal rows for each category and a grand total.

Execution Table

Step	category	product	SUM(sales)	Explanation
1	Electronics	Phone	1000	Sum sales for Electronics Phone
2	Electronics	Laptop	1500	Sum sales for Electronics Laptop
3	Electronics	—	2500	Subtotal for Electronics category (Phone + Laptop)
4	Furniture	Chair	700	Sum sales for Furniture Chair
5	Furniture	Table	300	Sum sales for Furniture Table
6	Furniture	—	1000	Subtotal for Furniture category (Chair + Table)
7	—	—	3500	Grand total for all categories and products

💡 All groups processed, subtotals and grand total rows added by ROLLUP

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	Final
category	null	Electronics	Electronics	Electronics	Furniture	Furniture	Furniture	null
product	null	Phone	Laptop	null	Chair	Table	null	null
SUM(sales)	0	1000	1500	2500	700	300	1000	3500

Key Moments - 3 Insights

Why does the product column show null in some rows?

Rows with null in product are subtotal rows for the category, as shown in execution_table rows 3 and 6.

What does the row with both category and product as null represent?

That row is the grand total for all categories and products, shown in execution_table row 7.

How does ROLLUP decide which subtotal rows to add?

ROLLUP adds subtotal rows by progressively removing columns from right to left in the GROUP BY, as seen in the subtotal rows with null product.

Visual Quiz - 3 Questions

Look at the execution_table, what is the SUM(sales) value at step 3?

A 2500

B 1500

C 1000

D 3500

💡 Hint

Check the row where category is Electronics and product is null in execution_table.

At which step does the grand total appear in the execution_table?

A Step 6

B Step 7

C Step 3

D Step 1

💡 Hint

Look for the row where both category and product are null.

If we remove product from GROUP BY and use ROLLUP on category only, how would the subtotal rows change?

A There would be no subtotal rows

B Subtotal rows would be for product only

C Subtotal rows would be for category only, no product breakdown

D Grand total row would be removed

💡 Hint

ROLLUP adds subtotals by removing columns from right to left in GROUP BY.

Concept Snapshot

ROLLUP adds subtotal and total rows automatically.
Use WITH ROLLUP after GROUP BY.
It groups by all columns, then adds subtotals by removing columns from right.
Nulls in grouped columns mark subtotal or total rows.
Useful for quick summary reports.

Full Transcript

ROLLUP is a SQL feature that helps you get subtotals and grand totals easily. When you group data by columns and add WITH ROLLUP, the database first groups rows normally. Then it adds extra rows that show subtotals for each group level by replacing the rightmost grouped columns with null. Finally, it adds a grand total row with all grouped columns as null. For example, grouping sales by category and product with ROLLUP will show sales per product, subtotals per category, and a grand total. Null values in grouped columns indicate subtotal or total rows. This makes reports easier to read and saves writing multiple queries.

Practice

1. What does the ROLLUP keyword do in a MySQL GROUP BY query?

easy

A. It adds subtotal and total rows automatically in the grouped result.

B. It filters out NULL values from the result set.

C. It sorts the result set in descending order.

D. It creates a backup of the grouped data.

Solution

1. Step 1: Understand GROUP BY basics

   GROUP BY groups rows by column values to aggregate data.

2. Step 2: Role of ROLLUP in GROUP BY

   ROLLUP adds extra rows showing subtotals and grand totals for grouped data.

3. Final Answer:

   It adds subtotal and total rows automatically in the grouped result. -> Option A

4. Quick Check:

   ROLLUP = subtotals and totals [OK]

Hint: ROLLUP adds summary rows automatically [OK]

Common Mistakes:

• Thinking ROLLUP filters data
• Confusing ROLLUP with ORDER BY
• Assuming ROLLUP creates backups

2. Which of the following is the correct syntax to use ROLLUP in a MySQL query?

easy

A. SELECT category, SUM(amount) FROM sales GROUP BY ROLLUP(category);

B. SELECT category, SUM(amount) FROM sales ROLLUP GROUP BY category;

C. SELECT category, SUM(amount) FROM sales GROUP BY category WITH ROLLUP;

D. SELECT category, SUM(amount) FROM sales GROUP BY category ROLLUP;

Solution

1. Step 1: Recall correct ROLLUP syntax

   In MySQL, ROLLUP is used as GROUP BY ... WITH ROLLUP.

2. Step 2: Analyze each option

   SELECT category, SUM(amount) FROM sales GROUP BY category WITH ROLLUP; uses GROUP BY category WITH ROLLUP, which is correct syntax. Others misuse ROLLUP placement or syntax.

3. Final Answer:

   SELECT category, SUM(amount) FROM sales GROUP BY category WITH ROLLUP; -> Option C

4. Quick Check:

   ROLLUP goes after GROUP BY with WITH keyword [OK]

Hint: Use 'GROUP BY ... WITH ROLLUP' syntax [OK]

Common Mistakes:

• Placing ROLLUP before GROUP BY
• Using ROLLUP as a function
• Omitting WITH keyword

3. Given the table sales with columns region, product, and amount, what will be the output of this query?

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

medium

A. Rows grouped by region and product, plus subtotals by region and a grand total row with NULLs.

B. Only rows grouped by region and product without any subtotal or total rows.

C. Rows grouped by product only, ignoring region.

D. Syntax error due to incorrect use of ROLLUP.

Solution

1. Step 1: Understand GROUP BY with multiple columns and ROLLUP

   ROLLUP creates subtotals for each grouping level: first by region and product, then by region only, then grand total.

2. Step 2: Interpret NULL values in output

   Subtotal rows have NULL in the column(s) rolled up. The grand total row has NULL in both region and product.

3. Final Answer:

   Rows grouped by region and product, plus subtotals by region and a grand total row with NULLs. -> Option A

4. Quick Check:

   ROLLUP adds subtotal and total rows with NULL markers [OK]

Hint: ROLLUP adds subtotal rows with NULLs in rolled-up columns [OK]

Common Mistakes:

• Expecting no subtotal rows
• Confusing grouping levels
• Thinking NULL means missing data

4. You wrote this query but it gives an error:

SELECT region, product, SUM(amount) FROM sales GROUP BY region, product ROLLUP;

What is the error and how to fix it?

medium

A. ROLLUP cannot be used with multiple columns in GROUP BY.

B. GROUP BY clause must come after WHERE clause.

C. SUM() function is not allowed with ROLLUP.

D. ROLLUP must be used with the keyword WITH after GROUP BY.

Solution

1. Step 1: Identify syntax error in ROLLUP usage

   ROLLUP must be used as GROUP BY ... WITH ROLLUP, not just appended after GROUP BY columns.

2. Step 2: Correct the query syntax

   Change query to GROUP BY region, product WITH ROLLUP to fix the error.

3. Final Answer:

   ROLLUP must be used with the keyword WITH after GROUP BY. -> Option D

4. Quick Check:

   ROLLUP requires WITH keyword [OK]

Hint: Use 'WITH ROLLUP' after GROUP BY columns [OK]

Common Mistakes:

• Omitting WITH keyword
• Misplacing ROLLUP before GROUP BY
• Thinking ROLLUP disallows multiple columns

5. You want to get sales totals by region and product, plus subtotals by region and a grand total. Which query correctly uses ROLLUP and filters out only the grand total row from the result?

hard

A. SELECT region, product, SUM(amount) FROM sales GROUP BY region, product WITH ROLLUP HAVING region IS NOT NULL;

B. SELECT region, product, SUM(amount) FROM sales GROUP BY region, product WITH ROLLUP HAVING NOT (region IS NULL AND product IS NULL);

C. SELECT region, product, SUM(amount) FROM sales GROUP BY region, product WITH ROLLUP HAVING product IS NOT NULL;

D. SELECT region, product, SUM(amount) FROM sales GROUP BY region, product WITH ROLLUP HAVING region IS NULL AND product IS NULL;

Solution

1. Step 1: Understand ROLLUP output rows

   ROLLUP adds subtotal rows with NULLs in rolled-up columns; grand total row has NULL in all grouped columns.

2. Step 2: Filter out only the grand total row

   The grand total row has both region and product as NULL. To exclude it, use HAVING NOT (region IS NULL AND product IS NULL).

3. Final Answer:

   SELECT region, product, SUM(amount) FROM sales GROUP BY region, product WITH ROLLUP HAVING NOT (region IS NULL AND product IS NULL); -> Option B

4. Quick Check:

   Exclude grand total by filtering NULLs in all grouping columns [OK]

Hint: Exclude grand total by HAVING NOT all grouping columns NULL [OK]

Common Mistakes:

• Filtering only one column NULL
• Filtering to keep only grand total instead of excluding
• Using WHERE instead of HAVING with GROUP BY