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.