ROLLUP for subtotals in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this ROLLUP query?

Given a sales table with columns region, category, and amount, what will be the output of this query?

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

MySQL

CREATE TABLE sales (region VARCHAR(20), category VARCHAR(20), amount INT);
INSERT INTO sales VALUES
('North', 'Books', 100),
('North', 'Electronics', 200),
('South', 'Books', 150),
('South', 'Electronics', 250);

A Syntax error because ROLLUP cannot be used with multiple columns

B Rows showing sums only for each region-category pair without any subtotal or grand total

C Rows showing sums for each category only, ignoring region

D Rows showing sums for each region-category pair, each region total, and a grand total row with NULLs in region and category

💡 Hint

ROLLUP adds subtotal rows for each grouping level and a grand total row.

ROLLUP creates subtotals for each grouping column from right to left and a grand total row with NULLs in all grouping columns.

🧠 Conceptual

intermediate

How does ROLLUP generate subtotal rows?

Which statement best describes how the ROLLUP operator works in a GROUP BY clause?

A It duplicates all rows without grouping

B It adds subtotal rows by progressively removing grouping columns from right to left

C It only adds a grand total row without subtotals

D It sorts the result set without aggregation

💡 Hint

Think about how grouping columns are reduced step by step.

ROLLUP creates subtotals by grouping on all columns, then removing the last column, then the second last, and so on, producing subtotals and a grand total.

📝 Syntax

advanced

Which query correctly uses ROLLUP for subtotals?

Choose the valid MySQL query that uses ROLLUP to get subtotals by department and role from an employees table.

A SELECT department, role, COUNT(*) FROM employees GROUP BY ROLLUP(department, role);

B SELECT department, role, COUNT(*) FROM employees ROLLUP BY department, role;

C SELECT department, role, COUNT(*) FROM employees GROUP BY department, role WITH ROLLUP;

D SELECT department, role, COUNT(*) FROM employees GROUP BY department, role ROLLUP;

💡 Hint

ROLLUP is used as a function inside GROUP BY in MySQL.

In MySQL, ROLLUP is used as GROUP BY ROLLUP(column1, column2). The syntax 'WITH ROLLUP' is deprecated but still works in some versions; however, the question asks for the correct modern syntax.

❓ optimization

advanced

How to optimize a query with ROLLUP on large data?

You have a large sales table and use GROUP BY ROLLUP(region, product). Which approach best improves performance?

A Use SELECT * instead of specifying columns to reduce parsing time

B Remove ROLLUP and calculate subtotals manually in application code

C Create indexes on the grouping columns <code>region</code> and <code>product</code>

D Add ORDER BY clauses on non-grouped columns

💡 Hint

Indexes help speed up grouping and aggregation.

Indexes on grouping columns help the database quickly group and aggregate data, improving performance of ROLLUP queries.

🔧 Debug

expert

Why does this ROLLUP query produce unexpected NULLs?

Consider this query:

SELECT department, role, SUM(salary) FROM employees GROUP BY ROLLUP(department, role);

Why do some rows have NULL in department or role?

A NULLs represent subtotal or grand total rows created by ROLLUP

B NULLs indicate missing data in the original table

C NULLs appear because the GROUP BY columns are not indexed

D The query has a syntax error causing NULLs to appear

💡 Hint

Think about what ROLLUP adds to the result set.

ROLLUP adds subtotal rows by grouping on fewer columns, which results in NULLs in the columns that are not grouped at that level.

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